How to filter records in an Access database (304259)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q304259
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 304263.
For a Microsoft Access 97 version of this article, see 304440.

SUMMARY

When you are working with data in an Access database, you may not want to view all of the data at once. Instead, you may want to view only those records that meet a certain criteria. To do so, you must apply a filter. This article shows you how to filter records in an Access database.

MORE INFORMATION

There are four methods that you can use to filter records in a form or a datasheet: Filter by Selection, Filter by Form, Filter for Input, and Advanced Filter/Sort.

Filter by Selection

  1. Start Microsoft Access, and then open the database that you are working with.
  2. In a field on a form, a subform, a datasheet, or a subdatasheet, select one instance of the value that you want to filter by (for example, a name or a number).
  3. On the Records menu, point to Filter, and then click Filter by Selection.
  4. Repeat steps 2 and 3 until you have the set of records that you are looking for.NOTE: You can also filter for records that do not have a certain value. After you select a value, right-click, and then click Filter Excluding Selection.

Filter by Form

  1. Open a form in Form view, or a table, a query, or a form in Datasheet view. To filter records in a subdatasheet, display the subdatasheet by clicking its expand indicator.
  2. On the Records menu, point to Filter, and then click Filter By Form to switch to the Filter By Form window.
  3. You can specify criteria for the form, the subform, the main datasheet, or any subdatasheet that is displayed. Each subform or subdatasheet has its own Look For and Or tabs.
  4. Click the field in which you want to specify the criteria.
  5. Enter your criteria by selecting the value that you are searching for from the list in the field (if the list includes field values), or by typing the value into the field.

    To find records in which a particular field is empty or not empty, type Is Null or Is Not Null into the field.
  6. To specify additional values that records can have in the filter, click the Or tab for the form, the subform, the datasheet, or the subdatasheet that you are filtering, and then enter more criteria.
  7. On the Filter menu, click Apply Filter to view the filter results.

Filter for Input

  1. Open a form in Form view, or a table, a query, or a form in Datasheet view. To filter records in a subdatasheet, display the subdatasheet by clicking its expand indicator.
  2. Right-click in the field in the form, the subform, the datasheet, or the subdatasheet that you are filtering, and then type the value that you are looking for in the Filter For box on the shortcut menu.
  3. Press ENTER to apply the filter, and then close the shortcut menu.

Advanced Filter/Sort

  1. Open a form in Form view, or a table, a query, or a form in Datasheet view.
  2. Click in the form, the subform, the datasheet, or the subdatasheet that you want to filter.
  3. On the Records menu, point to Filter, and then click Advanced Filter/Sort.
  4. Add the fields that you need to specify the values or the other criteria that the filter will use to find records to the design grid.
  5. To specify a sort order, click in the Sort cell for a field, click the arrow, and then select a sort order. Microsoft Access first sorts the leftmost field in the design grid, and then it sorts the next field to the right, and so on.
  6. In the Criteria cell for the fields that you have included, enter the value that you are looking for or enter an expression.
  7. On the Filter menu, click Apply Filter to view the filter's results.

REFERENCES

For more information about sorting data in Access, click the following article number to view the article in the Microsoft Knowledge Base:

304250 How to sort records in an Access database in Access 2002

Modification Type:MinorLast Reviewed:8/29/2006
Keywords:kbdownload kbfilters kbDatabase kbinfo kbhowto KB304259
©2004 Microsoft Corporation. All rights reserved.