ACC97: How to Filter Records in an Access Database (304440)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q304440
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

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, or a datasheet, 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.
  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, or the main datasheet that is displayed. Each subform 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, or the datasheet 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.
  2. Right-click in the field in the form, the subform, or the datasheet 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, or the datasheet 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 filtering records, click Microsoft Access Help on the Help menu, type filter records in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbhowto KB304440
©2003 Microsoft Corporation. All rights reserved.