Filtering Out Records

Instead of displaying all the records in a table, you can use a filter to display only those records that you want to see. You can display records based on a specific value in one field or based on multiple values in multiple fields. You can filter by selecting the field value on which to base the filter in Datasheet view or by using Filter By Form to help you create more complex filters involving multiple field values. After you apply a filter, Access displays only those records that match your specifications. You can remove a filter to return the datasheet to its original display.

Filter a Table by Selection

Display the table in Datasheet view.

Right-click the field value on which you want to base the filter.

Click Filter By Selection. Notice that the bottom of the Table window tells you the number of records matching your filter criteria. Also, the notation FLTR in the status bar indicates that a filter is currently in effect.

Clear a Filter from a Table

Display the table with the filter in Datasheet view.

Click the filtered table, and then click the Remove Filter button on the Table Database toolbar.

Did You Know?

You can save a filter as a query . Display the filtered table in Datasheet view, click the Records menu, point to Filter, and then click Advanced Filter/Sort. Click the Save As Query button on the Filter/Sort toolbar, type a name , and then click OK.

Save a Filter as a Query

Display the filtered table in Datasheet view.

Click the Records menu, point to Filter, and then click Advanced Filter/Sort.

The details of the filter appear in Design view.

Click the Save As Query button on the Filter/Sort toolbar.

Type the name you want to assign to the query. If you enter the name of an existing query, Access will ask if you want to overwrite the existing query. Be sure to answer "No" if you want to retain the original query, so you can give the new query a different name.

Click OK to save the filter as a query.

The query you have just saved appears in the Queries list in the Database window.

Click OK.