Using Data Filters

A filter is a subset of data from a table. Suppose that you want to see only certain records from a table, such as all customers who are past due. Instead of hiding the records that you don't want to see, create a filter. The filter removes unwanted records from view. The records don't go away, and you don't have to unhide the records later (as you do when you actually hide records by right-clicking over a column or row in the Datasheet view and selecting Hide Columns or Hide Row). A filter works similar to a short- term record hider, putting certain records out of the way while you work with the filtered data.

Access supports three filtering approaches:

  • Filter by Selection Filters data based on selected table data

  • Filter by Form Enables you to choose the data fields that you want Access to use for filtering

  • Advanced Filter/Sort Controls advanced filtering options from the Access menu bar

The easiest and most common filter options are Filter by Selection and Filter by Form, which the following two sections describe.


Access includes an Advanced Filter/Sort option on the Records, Filter menu, but you will almost always prefer creating a query to using the advanced filter. In addition, Access supports a Filter Excluding Selection that filters the opposite of Filter by Selection. A query is easier to save and work with in the future than a filter.

Filter by Selection

Filter by Selection works by example. Suppose that you want to display only those table records that contain a specific field value; for example, you need to work only with customer records from Brazil. If your customer table contains a Country field with scattered Brazil entries, you can filter out all those records that do not contain Brazil in their Country fields.

You can practice working with filters by opening the sample Northwind Traders database that comes with Access. (You'll locate this northwind.mdb file in your Office Samples folder.) When you first open the Northwind Traders database, a splash screen will appear; a splash screen acts as a title window that appears when you first start an application, and you can designate splash screens for databases that appear when you open them. Close the splash screen by clicking the window's OK button, and perform the following steps to design a filter that filters out records that don't contain Brazil:

  1. Display the Datasheet view for the Customers table. To do so, click the Tables entry inside the Database window and double-click the Customers table.

  2. Locate one record with Brazil in the Country field. You might have to click the horizontal scrollbar to see the Country field from the Datasheet view.

  3. Double-click the single field value that contains Brazil to select that value. If you select only the first part of the field instead of selecting the entire field, such as the letter B , you filter all records that do not start with B .

  4. Click the toolbar's Filter by Selection button. Access filters out all records that don't match your selected criteria . Figure 19.1 shows a filtered Datasheet view that displays only records containing a Brazil entry in their Country fields. Before the filter, this Datasheet view held more than 90 records. By locating the sample field and clicking once, you just changed the display to those records that only contain Brazil for the Country field value.

    Figure 19.1. Filter by Selection filters out all unwanted records.


  5. To return to the full Datasheet view, click the toolbar's Remove Filter button.

As you can see, a filter removes unwanted records; Access filters those unwanted records from view.


To filter out records that contain your selected value (instead of all records that do not contain a value), select Records, Filter, Filter Excluding Selection. The Filter Excluding Selection option works similarly to a reverse filter. Click the Remove Filter to revert the Datasheet view to its full table once again.

You do not first have to locate a customer record from Brazil to create the previous list's filter. If you right-click any field value in the table and then type a value in the shortcut menu's Filter for text box, Access applies the Filter by Selection command to your entered filter value. Therefore, you can right-click on the Country column, select Filter By, and then enter Brazil to more quickly create the same filter that the previous steps created.

For now, remove the filtering by clicking the Remove Filter button on the toolbar.

To Do: Perform a Filter by Form

Filter by Form enables you to filter by multiple values rather than by only one value. You can apply a Filter by Form from both the Datasheet and Form views.

Perform these steps to use Filter by Form:

  1. Click the Filter by Form button. Access displays a single blank record. If you have recently created a filter, that previous filter's selection value appears in the single row that displays. You can erase the value and enter a different one.

  2. Scroll to the field you want to filter by or click on the empty field.

  3. Click the field's drop-down arrow to display the list. Access displays a scrolling drop-down list of every value (without duplicates) that appears in that field within the current table.

  4. Select the value by which you want to filter.

  5. Optionally, click another empty field value and select from that field's drop-down list of available values. You can select as many filtering field values as you need. Unlike the Filter by Selection, which enables you to filter only by one field value, Filter by Form enables you to filter by several fields.

  6. Click the Apply Filter button to display the filtered records.

  7. Click the Remove Filter button (before you begin filtering, this button is called the Apply Filter button) when you are ready to return to the full-record view.

By filtering on one or more fields, you can easily display only those records you want to see. The filters do not actually remove records from your tables, but they help display the data you need to see at the moment.

If you filter by a date field, Access surrounds the filtered value with pound signs (#). You often see pound signs around dates, such as #7/4/2005#; this designation enables Access to distinguish the date from a formula that might use the forward slash ( / ) as the divide symbol.


Access can perform an Or filter, meaning that Access filters to find all records that include one or more of your selected filter field values. If you want to find all customers who live in New York or who live in Maine, for example, select New York, click the Or tab at the bottom of the Filter by Form window, and select Maine. You can continue adding Or conditions to select from one of several fields.


Although Access uses an Or condition to select from one of several field values, it uses an And condition to select across fields. This means that if you select Brazil for the Country field filter and Rio for the City field filter, Access filters to find all records with both Brazil as a country and Rio for the city. If a record contains the country Brazil but Sa Paulo for the city, such a filter would not retain that record.

Sams Teach Yourself Microsoft Office 2003 in 24 Hours
Sams Teach Yourself Microsoft Office 2003 in 24 Hours
ISBN: 0672325535
EAN: 2147483647
Year: 2003
Pages: 272
Authors: Greg Perry © 2008-2017.
If you may any questions please contact us: