How to Filter an Access List Box as Text Box Values Change
Question details
The user needs to filter the results of an Access list box dynamically as they type values into a text box.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering records in a form by typing search criteria into a text box, which updates the list box's RowSource query.
- Observed behavior
- The list box returns no results because the LIKE expression in the query is incorrectly formatted for either text or numeric fields.
Ensure your Access form is open in Design View and take note of the exact names of both your List Box and your Search Text Box.
Filter Alphanumeric Fields Using the Correct LIKE Syntax
Use this method when filtering text-based (alphanumeric) fields to ensure wildcard characters are properly formatted.
When filtering text fields, the SQL query must encapsulate the wildcard asterisks (*) in quotes and concatenate them with the text box value.
Select your List Box in Design View, open the Property Sheet, and click the ellipsis (...) next to the RowSource property.
In the Criteria row of your alphanumeric field (e.g., 'Code'), enter the following expression: Like '*' & Forms!YourFormName!YourTextBoxName & '*'
Select your Text Box, go to the Event tab in the Property Sheet, and add a VBA macro to the 'On Change' event: Me.YourListBoxName.Requery
Optimize Search Performance with a Prefix Search
Use this method if users only search from the beginning of a string, allowing Access to utilize indexing for faster results.
Filter Numeric Fields Correctly
Apply this solution when filtering number fields, as the LIKE operator should only be used for text.
Analyze Exported Data Easily with WPS Office
While Microsoft Access is a specialized database tool, you can easily export your filtered queries to a spreadsheet for easier sharing and analysis. WPS Spreadsheets offers a free, lightweight, and highly compatible alternative to Microsoft Excel for all your data analysis needs.
- 1. Export Your Data: Right-click your filtered Access query, select Export, and choose Excel to save it as an .xlsx file.
- 2. Open with WPS Spreadsheets: Launch WPS Office and open your newly exported spreadsheet.
- 3. Filter Without Code: Use the Data > Filter tool in WPS Spreadsheets to instantly search and filter your records without writing any SQL or VBA code.
Frequently Asked Questions
Why does my Access list box turn blank when I start typing?
This usually happens if the list box is not being requeried, or if the LIKE syntax is missing the correct concatenation. Ensure you are using the correct wildcard syntax (`Like '*' & [TextBox] & '*'`) and that you have added `Me.ListBox.Requery` to the text box's On Change event.
Can I use the LIKE operator on numeric fields in Access?
It is not recommended. The LIKE operator is designed specifically for string (text) comparisons. For numeric fields, you should use standard comparison operators like `=`, `>`, `<`, or `Between`.
How do I use the text box `.Text` property in VBA filtering?
If you trigger the filter on the 'On Change' event of the text box, the `.Value` property has not updated yet. You must use `Me.TextBoxName.Text` in your VBA code to capture the keystrokes as they happen.
Why is my database slow when filtering a list box?
Using a leading wildcard (e.g., `Like '*text*'`) prevents Access from using database indexes on that field. If performance is slow on a large dataset, remove the first asterisk to perform a prefix search (`Like 'text*'`), which allows Access to utilize indexes.
