Troubleshoot filters

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

ShowSaved filters are not getting applied to a view or report.

Often, filters are saved but not applied automatically. For more information, see the topic Limit the number of records in a view or report.

ShowI 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.

ShowI get an error message saying my expression is not valid.

See Troubleshoot expressions for more information.

ShowSome form fields are not available as criteria fields in the Filter By Form window.

ShowI 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.

ShowI want to use a query as a filter, but the query is not listed when I click Load from Query.

ShowThe 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.

ShowI want help with troubleshooting filters in an Access project.

See the topic Troubleshoot filters (ADP) for more information.

I applied a filter, but...

ShowSome records are missing (I see too few records).

ShowThe 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.

ShowThe 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.

ShowI 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.

ShowThe 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.

 Notes 

  • 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.

ShowI 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.
 
 
Applies to:
Access 2003