Filter: Limit the number of records in a view

To find one or more specific records in a form, or to print specific records in a report, table, or query, you can use a filter. A filter limits a view of data to specific records without requiring you to alter the design of the underlying query, form, or report. For example, rather than scanning a 100-page product list to find items in the $50 to $100 price range, or creating a smaller report to show only such items, you can instead apply a filter to the data to show only the records in the Products table where the Price field has a value between $50 and $100.

In this article


This article describes how to apply and save filters. This article does not cover how to use graphics filters or filtering in other Office programs, such as Microsoft Office Excel 2007 or Microsoft Office Outlook 2007. See the Help in those programs for more information.

 Note   If you are looking for a way to filter for records that contain top or bottom values, or for records that contain unique or duplicate values, filtering is not the right solution. For more information, see the articles listed in the See also section.

Introduction to filters

A filter changes the data that a form or report displays in a view without altering the design of the form or report. You can think of a filter as a criterion or a rule that you specify for a field. The criterion identifies the field values that you are interested in viewing. When you apply the filter, only records that contain the values that you are interested in are included in the view. The rest are hidden until you remove the filter.

 Note   Columns or controls that are bound to expressions do not support filtering.

Filters are easy to apply and remove. In Microsoft Office Access 2007, common filters are built into every view that displays data, including Layout view. The availability of filter commands depends on the type and values of the field.

For example, you can view the records of only those people whose birthdays fall during a specific month by clicking the appropriate menu commands.


Filtering on a date field

Callout 1 The filters that are available to you depend on the type of data that is in the selected column.
Callout 2 All Dates in Period filters ignore the day and year portion of the date values.

When a filter is applied, the view is updated to show only the records that match your criteria. In this case, you see only those customer records in which the month portion of the BirthDate field is set to April. All other records are hidden.


BirthDate filtered for April

Callout 1 Icons in the column header and the record navigator bar indicate that the current view is filtered on the BirthDate column.
Callout 2 Hovering the mouse over the column heading displays a tip showing the current filter criterion.

When you apply a filter to a column that is already filtered, the previous filter is removed before the new filter is applied. For example, if the BirthDate field is already filtered to show birthdays that fall in April, when you apply a filter to see birthdays that fall in February, the result will include only birthdays that fall in February. Access automatically removes the filter that returned only birthdays in April before applying the second filter.

Though only a single filter will be in effect for any one field at any one time, you can specify a different filter for each field that is present in the view. For example, to see the names of those contacts who 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

You can work with the filtered results in the same way that you work with the initial view — for example, you can edit the data, and you can navigate to other records.

You can revert to the unfiltered view by removing the filters. Removing a filter temporarily removes it from the view, so that you can switch back to the original, unfiltered view. In the Sort & Filter group on the Home tab, you can click Toggle filter to switch between filtered and unfiltered views.

You can also permanently remove a filter from a view by clearing the filter. To learn how to clear filters, see the section Clear or save a filter.

Filter settings will be 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 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.

Whether the filter settings will be in effect when you open the object the next time depends on the FilterOnLoad property setting for the object. If you set the FilterOnLoad property of a table, query, form, or report to Yes, the filter settings that were last applied will be applied when you reopen the object.

 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.

Top of Page Top of Page

Types of filters


Office Access 2007 offers several ready-to-use filters for each 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.

Following is a little more information about each type of filter that is available to you in Access.

Common filters

Several popular filters are available as context menu commands, so that you don't have to spend time constructing the correct filter criteria. To access these commands, right-click the field you want to filter.

 Note   If you select two or more columns or controls, the filter options will not be available. If you want to filter the view 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.

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.

For example, to see the filters available for the BirthDate field:

On the Home tab, in the Sort & Filter group, click Filter.


Common date filters

Common date filters
Callout 1 To filter for specific values, use the check box list. The list displays all the values that are currently displayed in the field.
Callout 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, use the value list. In an unfiltered view, the value list for each field presents all of the unique values that are stored in that field.

 Note   When a view is filtered, the value list for each field shows only the unique values that are visible in the view. For example, if the CountryRegion field is filtered to show only records that have the value USA, the value list of the Full Name field will show only the names of those who live in the USA. To see more values in the value list, remove some or all filters from the view.

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.

Long and short common date filter lists

Long and short common date filter lists

 Note   Type-specific filters are not available for Yes/No, OLE object, and attachments fields. The values list is not available for memo fields, or for fields that contain rich text.

Filter based on selection

If the value that you want to use as the basis for filtering is currently selected, you can quickly filter the view by clicking one of the Selection commands. The commands that are available will vary, depending on the data type of the selected value. These commands are also available on the context menu of the field, accessed by right-clicking the field.

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.

Selection-based filter list

The command list automatically includes the current value, so that you don't have to type it.

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.


Filters based on partially-selected field

Callout 1 Filter using the beginning of a field value...
Callout 2 ...the middle of a field value...
Callout 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.

Filter by form

This technique 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 Memo, 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 form and, on the Home tab, in the Sort & Filter group, click Advanced Filter/Sort, and then click Filter by Form.

Enter the first set of values, then click the Or tab, 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 toggle the filter:    

On the Home tab, in the Sort & Filter group, click Toggle Filter.

Access shows only those records that match your input.

Advanced filters

On occasion, you might want to apply a filter that is not in the common filters list. For example, if you want to find records that contain dates that occur during the past seven days or the past six months, you might have to write the filter criterion yourself.

Using this feature requires that you are familiar with writing expressions. Expressions are similar to formulas that you write in Excel, and to the criteria that you specify when designing a query.

For example, if you want to find the names of those contacts whose birthdays occurred during the past seven days, you can use an advanced filter.

  • On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

You add the filter fields to the design grid, and specify the filter criteria in the Criteria row.

When you click Toggle Filter in the Sort & Filter group on the Home tab, Access displays the filtered records.

Continuing with the same example, if you want to further limit the results to those whose country/region is USA, switch back to the Filter object tab, add the CountryRegion field to the grid, and then specify a criterion.

Top of Page Top of Page

Apply a filter


 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.

Apply a common filter

  1. Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
  2. 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.

  1. Click anywhere in the column or control that corresponds to the first field that you want to filter.
  2. On the Home tab, in the Sort & Filter group, click Filter.
  1. Do one of the following:
  • 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.

Hint    If you have a large list of values, and if you want to filter on one or only a few of those values, 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).
  1. Repeat the previous three steps for each field that you want to filter.

Apply a filter based on a selection

  1. Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
  2. 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.
  3. 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.
  4. On the Home tab, in the Sort & Filter group, click Selection, and then click the filter you want to apply.
  1. To filter other fields based on a selection, repeat the preceding two steps.

Apply a filter by filling out a form

  1. Open a table or query in Datasheet view, or a form in Form view.
  2. 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.
  3. On the Home tab, in the Sort & Filter group, click Advanced, and then click Filter by Form on the shortcut menu.
  1. Depending on whether you are working in Datasheet view or in Form view, do one of the following:
    • In Datasheet view, click the first row in the column on which you want to filter. Click the arrow that appears, and select a value. You can add additional values by clicking the Or tab at the bottom of the datasheet and selecting another value.
    • In Form view, click the arrow that appears in the control, and select a value on which to filter. You can add additional values by clicking the Or tab at the bottom of the form and selecting another value. Click the Or tab to add even more values.
    •  Note   You cannot specify field values for multivalued fields using filter by form, although you can specify values for any non-multivalued field in the recordset.

    • ShowIf you do not see a list of field values when you click the drop-down arrow for a field

  • If you see only the values Is Null and Is Not Null displayed when you click the drop-down arrow for a field, or if there is no drop-down arrow, do the following to display the actual field values:
  • Verify that the data type for the field is not Memo, Hyperlink, Yes/No, or OLE Object. Fields of these data types cannot be made to display field values on the Filter By Form object tab. Multivalued fields similarly cannot display values on the Filter By Form object tab.
  • If the number of records read is more than the number specified in the Default max records box in the Filter lookup options for Database section in the Access Options dialog box, Access doesn't display values for that field on the list. To open the Access Options dialog box, click the Microsoft Office ButtonButton image, and then click Access Options. Click Editing, then change the Default max records setting to work around this problem.
  • In the Access Options dialog box, click Editing. In the Filter lookup options for Database section, select the Local indexed fields and Local nonindexed fields check boxes.
  • Switch back to Datasheet view or Form view.

  • To specify a list of possible values for a field, use the or operator. For example, you can 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.
  1. Sometimes, you might want to specify two alternate sets of criteria. For example, you might want to see the names of only those contacts whose CountryRegion value is USA and whose birthdays fall in April, or the names of only those contacts whose CountryRegion value is Asia and whose birthdays fall in May.
    • To retrieve all records that meet any one of multiple sets of criteria, you need to specify the criteria somewhat differently.
    • Enter the first set of criteria, then 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.
    • 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.
  2. To remove the filter and show all records, click Toggle Filter again.
  3. To modify the filter, click Filter By Form again.

The current filter criteria set is displayed.

Apply an advanced filter

  1. Open a table, query, form, or report in any of the following views: Datasheet, Form, Report, or Layout.
  2. 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).
  3. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.
  1. Add the fields on which you want to filter to the grid.
  2. 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.

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

  1. Click Toggle Filter to see the filtered rows.

 Tip   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:

  • On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

Review the result, and then revise the criteria displayed in the Criteria row to produce the desired result.

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

Special filter options

The Load from Query command loads the design of a selected query into the grid. This lets you use the query critieria as filter criteria. The Save As Query command lets you save the filter settings as a new query.

Top of Page Top of Page

Remove or reapply a filter

If you want to switch to the unfiltered view of data, remove the filters.

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

  • Click Unfiltered on the record navigator bar to reapply the most recent filters.

The most recent filter settings are reapplied to the view. To continue the previous example, the previously applied filters on the CountryRegion and BirthDate fields are reapplied.

Top of Page Top of Page

Clear or save 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.

It is easy to save filters for future use. 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 to you 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, set the object's FilterOnLoad property to Yes. You set this property in the object's property sheet. The FilterOnLoad property is applied when you open the object; when you change this setting, you must close the object and reopen it in order to apply the new setting.

Top of Page Top of Page

 
 
Applies to:
Access 2007