If you are looking for help about filtering in a Microsoft Office program other than Microsoft Access, see the Help for that program — or you can look in the See Also list in this topic for links to specific Help topics about filtering in other programs. For help on issues with graphic filters in Access, see Use images in your Access forms, reports, and controls.
If you are new to filtering in Access, see the topic Filter: Limit the number of records in a view or report to learn the basics and the various filtering techniques.
I am having problems applying a filter
Saved filters are not getting applied to a view or report.
I don't see lists of field values in the Filter By Form window.
If you only see the values Is Null and Is Not Null displayed in the list when you click the arrow in the field, do the following to display the actual field values:
- Verify that the field is not of the following data types: memo, hyperlink, Yes/No, or OLE object. Fields of these data types cannot be made to display field values in the Filter By Form window.
- If the number of records read is more than the number specified in the Don't display lists where more than this number of records read box on the Edit/Find tab of the Options dialog box, Access doesn't display values for that field in the list. Consequently, you need to change the setting for this option. Because Access reads all values in non-indexed fields, not just unique values, set this option to a number greater than (or equal to) the number of records in the non-indexed field with the most values. Note that when Access is able to display the list in a field, it only shows the unique values, even for a non-indexed field.
- On the Tools menu, click Options. On the Edit/Find tab, under Show list of values in, select all the check boxes. Then switch back to Datasheet view or Form view.
I get an error message saying my expression is not valid.
Some form fields are not available as criteria fields in the Filter By Form window.
When filtering a form, you cannot use the following fields as criteria fields:
I cannot filter based on a combo box by using the Filter By Selection technique.
You will encounter this situation when the combo box is filled with a value list and its bound column is not the first displayed column in the list. When you select just part of the text in the text box portion of a combo box, Access cannot match the text selected in the displayed column with the data from the bound column that is actually stored in the underlying table's field. For more information about how to resolve this, see Filter By Selection fails in value-list-filled combo box.
I want to use a query as a filter, but the query is not listed when I click Load from Query.
Only a query that meets the following requirements will appear in the Applicable Filter dialog box when you click Load from Query.
- Must be based on the same underlying table or query as the form or datasheet to which you're applying it.
- Can't include other tables or queries.
- Must be a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) (not a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) or action query (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.)).
- Can't include fields that sum, count, or calculate another type of total for the values in a field.
The filter commands appear dimmed and are unavailable in Datasheet view and Form view.
It is possible to prevent filtering in a form by setting the form's AllowFilters property to No. This setting makes unavailable the Filter by Selection, Filter by Form, Filter for Input, and Advanced Filter/Sort techniques. For further assistance, contact the form designer.
I want help with troubleshooting filters in an Access project.
I applied a filter, but...
Some records are missing (I see too few records).
The filtered view shows too many records.
If you see too many records, review the current filter criteria, and then add more filters or modify the expressions behind the current filter. Note that if you apply a filter to an already-filtered view, the criteria behind the new filter gets combined with the existing criteria by using the and operator. If you don't want the filters to be cumulative, click Remove Filter on the Datasheet view or Form view toolbar or Filter Toggle on the Page view toolbar to remove all current filters and start over again.
Note You might also want to verify that the filter is actually applied to the view or report. Often, filters are saved with a view or report but are not automatically applied. For more information on how to identify if and what filters are applied and when filters are applied automatically, see the topic Limit the number of records in a view or report.
Exclude unnecessary records Select a record containing a value that you do not want, right-click the selection, and then click Filter Excluding Selection on the shortcut menu. When using this technique, make sure you haven't selected values in more than one row at a time. Although you can do this, Access interprets the filter criteria as follows: "exclude the first selected value OR the second selected value," instead of "exclude the first selected value AND the second selected value." Instead, select the first value that you want to exclude, right-click it, and then click Filter Excluding Selection. Select the next value that you want to exclude, and then repeat the previous step.
Add a criteria field In the Filter By Form window, add an expression for a field. In the Advanced Filter/Sort window, add a field to the grid, and then specify an expression in the Criteria row.
Remove alternate criteria Review and delete some of the alternate criteria specified on the Or tab (Filter By Form window) or in the or row (Advanced Filter/Sort window).
Replace the Or operator with And operator Move alternate criteria specified on the Or tab to the Look For tab in the Filter By Form window, or from the Or row to the Criteria row in the Advanced Filter/Sort window.
The filter returnset looks different from what I think it should be.
If the filter is returning the wrong records, make sure you're using the correct fields to specify the value you're searching for or to specify other criteria, and make sure you've entered the correct value or criteria. If you're filtering on fields from linked tables, the values that you use in criteria for those fields are case-sensitive (case-sensitive: Capable of distinguishing between uppercase and lowercase letters. A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.) — they must match the case of the values in the underlying table.
Remember that filters are additive — that is, if the view is already filtered when you apply a filter, the new filter gets combined with the existing filters by using the and operator, so you are more likely to see fewer records.
If you are working on a shared database, refresh your view periodically to make sure your view is up to date. To refresh Datasheet view or Form view, click Apply Filter/Sort on the Records menu. To refresh a report or page, simply close and reopen it.
If you're using expressions in the Filter By Form window or Advanced Filter/Sort window, verify that the expressions are correct.
If you're specifying multiple criteria in the Advanced Filter/Sort window, verify that you've entered the criteria in the correct rows and columns in the design grid. If you're specifying multiple criteria in the Filter By Form window, verify that you've entered the expressions on the correct tabs. The placement of criteria determines whether Access uses the and or the or operator, which in turn affects the returnset.
I filtered the subform or subdatasheet, but it did not filter the main form or datasheet.
The filter behaved correctly. When you create a filter on a subform or a subdatasheet, it only applies to the subform or subdatasheet. To filter the records on the main form or datasheet, click the main form or datasheet, and then create the filter.
The returnset is not sorted properly.
You may need to:
- Add or delete a sort order.
- Sort by a different field.
- Change the sort order from Ascending to Descending, or vice versa.
- In the Advanced Filter/Sort window, rearrange the columns in the design grid, and then specify a sort order on one or more fields. Access sorts the records starting with the leftmost column in the grid.
- If you have numbers in fields with a text data type, those numbers will not sort from lowest number to highest, or vice versa. Instead, they will sort as follows: 1, 11, 12, 2, 20, 3, and so on. You must precede the single-digit numbers with a leading zero for the strings to be sorted properly.
- If you have Null values in a field that you're sorting in ascending order, records with those values appear first in the sort order before any records with text or numeric values in the sorted field.
I want to test my filter before I distribute the database.
Before you save a filter or distribute your application, you might want to test your filter to verify that it works the way it is supposed to. If you are comfortable with writing Visual Basic® for Applications (VBA) code, see How to test a user-defined filter using the Filter By Form feature to verify the filters that use the Filter By Form technique.
Still looking for help?
- Search the Microsoft Product Support Center The Support Center has a wealth of troubleshooting information. Search the Access Knowledge Base articles for help about issues specific to Access.
- Ask the Access experts and other users If a specific error or issue is not covered in this topic, post it to the Access experts and get help specific to your situation. For more information about how to get help from the Access community, see Ask the Access community.