Filtering is a useful way to see only the data that you want displayed in Access databases. You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query. By applying a filter, you are able to limit the data in a view without altering the design of the underlying object.
In this article
How filters are useful
Since the view you get after you apply a filter contains only records with the values that you selected, the rest of the data remains hidden until you clear the filter.
Note: Columns in datasheets and controls in forms and reports that are bound to expressions do not support filtering.
There are several types of filters and some of them are easy to apply and remove. Access contains some common filters that are built into every view. The availability of filter commands depends on the type and values of the field.
For example, to view the records of people whose birthdays fall during a specific month, click the BirthDate column, on the Home tab in the Sort & Filter group, click Filter, then click Date Filters in the dialog box, and then select the required date period.
1. The filters that are available to you depend on the data type in the selected column.
2. All Dates in Period filter ignores the day and year portion of the date values.
In this filtered view, you see only the records in which the month portion of the BirthDate field is set to April.
1. The filter icon in the column header and the record navigator bar indicates that the current view is filtered on the BirthDate column.
2. In the datasheet view, when you hover, the mouse, over the column heading, you will see the current filter criterion.
Note: When you apply a filter to a column that is already filtered, the previous filter is automatically removed. Though only a single filter can be in effect for a field at a time, you can specify a different filter for each field in the view.
For example, to see the names of contacts that live in the UK whose birthdays fall in April, you can filter the Contacts table on the CountryRegion field and also on the BirthDate field. When you filter multiple fields in a single view, the filters are combined by using the AND operator, like this:
CountryRegion = UK AND month of BirthDate = April
To revert to unfiltered view: Remove the filters. Removing a filter temporarily removes it from the view, so that you can switch back to the original, unfiltered view.
To switch between the filtered and unfiltered views: in the Sort & Filter group on the Home tab, click Toggle filter.
To permanently remove a filter from a view: Clear the filter. To learn how to clear filters, see the section Clear a filter.
Filter settings will remain in effect until you close the object, even if you switch to another view of the object. In other words, if you filter a form in Datasheet view, the filter settings will still be in effect even if you switch to Form view or Layout view, and will remain in effect until you close the form. If you save the object while the filter is applied, it will be available the next time that you open the object. To learn about saving a filter, see the section Save a filter in this article.
Select and apply a filter type
You can select from several ready-to-use filters for a data type. These filters are available as menu commands in the following views: Datasheet, Form, Report, and Layout. In addition to these filters, you can also filter a form or datasheet by completing a form (called Filter by Form).
If you want more flexibility, and if you are comfortable writing expressions, you can create your own filters by using the options on the Filter document tab.
Select from the following types of filters:
Common filters: To filter for specific values or a range of values.
Filter by Selection: To filter all the rows in a table that contain a value that matches a selected value in a row by filtering the datasheet view.
Filter by form: To filter on several fields in a form or datasheet, or if you are trying to find a specific record.
Advanced filter: To filter type where you define custom filter criteria.
Note: If you do not see the filter commands in any of the views, the designer of the form or database might have disabled filtering. Contact the designer for further assistance.
Common filters
Except for OLE Object fields and fields that display calculated values, all field types offer common filters. The list of filters that are available depends on the selected field's data type and values.
Right-click the field you want to filter. To filter on multiple columns or controls, you must either select and filter each column or control separately, or use an advanced filter option. See the Filter by form and Advanced filters sections in this article for more information.
For example, to see the filters available for the BirthDate field, on the Home tab, in the Sort & Filter group, click Filter.
1. To filter for specific values, use the check box list. The list displays all the values that are currently displayed in the field.
2. To filter for a range of values, click one of these filters, and specify the required values. For example, to see birthdays that fall between the current date and the end of the year, click Between, and then specify the appropriate start and end dates in the Between dialog box. To see all birthdays that fall on a specific date, click the date and all the birthdays that fall on that specific date are displayed.
It is important to note that the values in a date field have an impact on the list of type-specific filters. If the most recent date value in a date field falls within the past two years, you see a longer, more detailed filter list. If none of the dates in a field is less than two years old, you see the shorter filter list.
Note: Type-specific filters are not available for Yes/No, OLE object, and attachments fields. The values list is not available for Long Text fields, or for fields that contain rich text.
Apply a common filter
-
Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
-
Make sure that the view is not already filtered. On the record selector bar, verify that either the Unfiltered or the dimmed No Filter icon is present.
Tip: To remove all the filters for a particular object, on the Home tab, in the Sort & Filter group, click Advanced, and then click Clear All Filters.
-
Click anywhere in the column or control that corresponds to the first field that you want to filter and, on the Home tab, in the Sort & Filter group, click Filter.
To apply a common filter: Point to Text (or Number or Date) Filters, and then click the filter that you want. Filters such as Equals and Between prompt you to enter the necessary values.
Tip: Certain characters, such as *, %, and ?, have a special meaning when specified in a filter text box. For example, * represents a string of characters, so the string a* will match any string that begins with a, and not only the literal string a*. To disregard the special meaning of a character, enclose it in square brackets [], like this: a[*]. Databases that use the ANSI-89 standard treat *, ?, [, ], !, -, and # as special characters. Databases that use the ANSI-92 standard treat %, _, [, ], ^, and - as special characters. You can use either standard with Access, but you cannot mix the two standards (e.g., ?a*) in a particular expression..
To apply a filter based on field values: Clear the check boxes next to the values on which you do not want to filter, and then click OK.
To filter on one or only a few of values in a long list, first clear the (Select All) check box and then select the values you want.
To filter for null values (a null value indicates the absence of data) in text, number, and date fields: In the check box list, clear the (Select All) check box, and then select the check box next to (Blanks).
Filter by Selection
To see all the rows in a table that contain a value that matches the value in a row, you can quickly filter the datasheet view by selecting a specific value, and then clicking the Selection command. The drop-down list displays the available filtering options. These options will vary, depending on the data type of the selected value. Another way to access the selection filter options is to right-click the specific cell.
For example, if the value 2/21/1967 is currently selected in the BirthDate field, on the Home tab, in the Sort & Filter group, click Selection to display the filter by selection commands, and then select your filtering option..
The list of commands also depends on how much of the value is selected. For example, if you select only some of the characters in the value, you see a different list of commands, depending on which part of the field you selected.
1. Filter using the beginning of a field value...
2. ...the middle of a field value...
3. ...or the end of a field value.
Note: Filtering on a partial selection is not available for multivalued fields. The Selection command is not available for attachments.
To remove a filter, on the Home tab, in the Sort & Filter group, click Toggle Filter, or click Advanced and click Clear All Filters.
Apply a filter based on a selection
-
Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
-
Make sure that the view is not already filtered. On the record selector bar, verify that either the Unfiltered or the dimmed No Filter icon is present.
-
Move to the record that contains the value that you want to use as part of the filter, and then click inside the column (in Datasheet view) or control (in Form, Report, or Layout view).
To filter based a partial selection; select the characters that you want, on the Home tab, in the Sort & Filter group, click Selection, and then click the filter you want to apply.
Filter by form
This filter is useful when you want to filter on several fields in a form or datasheet, or if you are trying to find a specific record. Access creates a blank form or datasheet that is similar to the original form or datasheet, and then allows you to complete as many of the fields as you want to. When you are done, Access finds the records that contain the specified values.
Note: You cannot specify field values for multivalued fields using filter by form, nor for fields with Long Text, Hyperlink, Yes/No, or OLE Object data type, although you can specify values for other fields in the recordset.
For example, if you want to find all Customer records where the contact person's title is Owner, and that person is located either in Portland or in Eugene, open the Customers datasheet or form and, on the Home tab, in the Sort & Filter group, click Advanced, and then click Filter by Form.
Enter the first set of values, then click the Or tab at the bottom of the datasheet or form, and then enter the next set of values. Note that if you want a field value to operate as a filter independently of other field values, you must enter that value on the Look for tab and each Or tab. Each Or tab represents an alternate set of filter values.
To see only the records matching your input: On the Home tab, in the Sort & Filter group, click Toggle Filter.
Apply a filter by filling out a form
-
Open a table or query in Datasheet view, or a form in Form view.
-
Make sure the view is not already filtered. On the record selector bar, verify that either the Unfiltered or the dimmed No Filter icon is present.
-
On the Home tab, in the Sort & Filter group, click Advanced, and then click Filter by Form on the shortcut menu.
-
Depending on whether you are working in Datasheet view or in Form view, do one of the following:
Datasheet view: Click the first row in the column on which you want to filter, click the arrow that appears, and then select a value. To add additional values, just click the Or tab at the bottom of the datasheet and select another value.
Form view: Click the arrow that appears in the control, and select a value on which to filter. To add additional values, click the Or tab at the bottom of the form and select another value.
Tips: You cannot specify field values for multivalued fields by using the filter by form, but you can specify values for a non-multivalued field in the recordset.
-
To specify a list of possible values for a field, use the or operator. For example, specify "Portland" or "Oregon" in the City field to filter for records containing either value.
-
To filter based on the status of a control, such as a check box or button, click the control so that it is in the state that you want. To return the control to a neutral position, so that it won't be used as a criterion for filtering records, ensure that it is unavailable (appears dimmed).
-
To filter for records that have null (missing), non-null, blank (empty or ""), or nonblank values, type Is Null, Is Not Null, "", or Not "" in the field.
-
-
If you want to specify two alternate sets of criteria, for example, to only see the names of contacts whose CountryRegion value is USA and whose birthdays fall in April, you can do any of the following:
-
To retrieve all records that meet any one of multiple sets of criteria, specify the criteria by entering the first set of criteria, Click the Or tab, and then enter the next set of criteria. Note that if you want a field value to operate as a filter independently of other field values, you must enter that value on the Look for tab and each Or tab. In other words, the Look for tab and each Or tab represents an alternate set of filter values.
-
Also note that each time you add a field criterion to the Or tab, Access creates another Or tab. This enables you to specify several "or" criteria. The filter returns any record that contains all of the values specified on the Look for tab, or all of the values specified on the first Or tab, or all of the values specified on the second Or tab, and so on.
-
To remove a filter and show all records, click Toggle Filter again.
To modify a filter by form, click Advanced, then click Filter By Form again. The current filter criteria set is displayed.
Advanced filter
On occasion, you might want to apply a filter that is an advanced filter type and you might have to write the filter criterion yourself. For example, to find records that contain dates occurring during the past seven days or the past six months.
Using the advanced filters requires writing expressions. Expressions are similar to formulas in Excel, and to the criteria that you specify when designing a query.
One example of where you might use an advanced filter is, to find the names of those contacts whose birthdays occurred during the past seven days. After you apply an advanced filter, you can further limit the results to those whose country/region is USA.
Apply an advanced filter
-
Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
-
Make sure that the view is not already filtered. On the record navigator bar, verify that No Filter appears dimmed (is unavailable). If the record navigator bar is not visible, click Advanced in the Sort & Filter group on the Home tab, and then click Clear All Filters (if Clear All Filters appears dimmed, there are no filters in effect).
-
On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.
-
Add the fields on which you want to filter to the grid.
-
In the Criteria row of each field, specify a criterion. The criteria are applied as a set, and only records that match all of the criteria in the Criteria row are displayed. To specify alternate criteria for a single field, type the first criterion in the Criteria row and the second criterion in the Or row, and so on.
Tips:
-
The entire set of criteria in the Or row is applied as an alternative to the set of criteria in the Criteria row. Any criterion that you want to be applied for both sets of criteria must be typed in both the Criteria row and the Or row. Click Toggle Filter to see the filtered rows.
-
A good way to learn to write criteria is to apply a common filter or a filter based on selection that produces a result that is close to what you are looking for. Then, with the filter applied to the view, display the Filter object tab
-
Special commands on the Filter document tab
Two special commands are available to you on the Filter document tab. When you right-click anywhere above the design grid on the tab, the Load from Query and Save As Query commands are available on the shortcut menu.
The Load from Query command loads the design of a selected query into the grid. This lets you use the query criteria as filter criteria.
The Save As Query command lets you save the filter settings as a new query.
Remove or reapply a filter
To switch to the unfiltered view of data, remove the filters by clicking Filtered on the record navigator bar to revert to the full view.
When you remove the current filters, the filters are temporarily removed from all of the fields in the view. For example, if you first apply filters on the CountryRegion and BirthDate fields, and then remove the filters, you see all of the records again.
To reapply the most recent filters, click Unfiltered on the record navigator bar.
Clear a filter
Clear a filter when you no longer need it. Clearing a filter deletes it from the view, and you can no longer reapply it by clicking Unfiltered on the status bar. You can clear a single filter from a single field, or clear all filters from all fields in the view.
-
To clear a single filter from a single field: Right-click the filtered column or control, and then click Clear filter from field name.
-
To clear all filters from all fields: On the Home tab, in the Sort & Filter group, click Advanced, and then click Clear All Filters on the shortcut menu.
Save a filter
It might be useful to save a filter if you will be using it again. The filter settings that are in effect at the time when you close a table, query, form, or report are automatically saved along with the object, and they are available for reapplying. However, by default, the filter settings are not automatically applied for you when you next open the object.
To ensure that the current filters are automatically applied when you next open a table, query, form, or report, from the object's property sheet, set the object's FilterOnLoad property to Yes. The FilterOnLoad property is applied the next time you open the object. Whenever you change this setting, you must close the object and reopen it in order to apply the new setting.
Note: The FilterOnLoad property setting is only applied when the object loads. Setting this property for an object in Design view and then switching to another view will not cause the setting to be applied. You must close and reopen the object for changes to the FilterOnLoad property setting to take effect..
