A filter lets you see specific records in a table, form, query, or report. For example, you can filter the records in an Orders table to view only orders that were delivered in June, were made by customers whose names begin with H, or were shipped by Acme Freight. You can also use a filter to find orders that were shipped in June by Acme Freight to customers whose names begin with H.

Filters are closely related to queries, but they differ in one important way. Filters are temporary snapshots of records that meet specific criteria. In contrast, queries are objects in the Database window with the potential for a long-lasting life. But the transitory nature of a filter can easily be remedied by saving it as a query.

There are several ways to create filters. Filter By Form in not the simplest or fastest method, but it offers more versatility than most other techniques. Let’s create some filters using Filter By Form and save the last one as a query.

Create A Filter

Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can open and use without worrying about changing the original file. If you prefer to work with the original Northwind database, open Access and click Help, Sample Databases, and Northwind Sample Database.

Open the Products table and use the horizontal scrollbar to view the various fields it contains. Suppose you want to see only those products that are condiments. Click Filter By Form (the button with a funnel and a form). Click in the empty cell below Category. Click the Down arrow button, which contains all the categories in the table. Select Condiments. Click Apply Filter (the button with a funnel). The table now displays only products that are condiments. Click Filter By Form.

Suppose you want to see only condiments that have a reorder level of 25. In Filter By Form, as in the design grid of the query window, criteria on the same row are AND criteria. In other words, when you enter criteria on the same row, all the conditions must be true for Access to retrieve the record.

Scroll to the right and click in the cell under Reorder Level. (Try clicking the right side of the cell so that the Down arrow menu opens automatically.) Click 25 from the list. Click Apply Filter. You can use the horizontal scroll bar to verify that only condiments with a reorder level of 25 are displayed. Click Filter By Form and click Clear Grid (the button is a red X).

Enter Expressions

When you use Filter By Form, your choices are not confined to selections in the drop-down menu. You can also enter expressions.

For example, let’s say you want to find beverages that have more than 29 units in stock. First, click the Categories drop-down button and select Beverages. Next, click in Units In Stock and type >29. (Click at the left of the cell to avoid opening the drop-down menu.) Click Apply Filter. The beverage products listed all have more than 29 units in stock. Click Filter By Form. Click Clear Grid.

Use Two Types Of Criteria

Suppose you’re Northwind’s manager, and you’re thinking of putting some products on sale. The most likely candidates have more than 50 units in stock and have a unit price of $25 or more. At the same time, you’d like to consider for sale all products with a 0 (zero) Reorder Level.

To find the two sets of products, you will be using both AND and OR criteria. You use AND criteria to find products with more than 50 units in stock with a unit price of $25 or more. To find all products with a Reorder Level of 0, you’ll use OR criteria.

To find products that have more than 50 units in stock, type >50 in the Units In Stock column. To find products that, at the same time, cost more than $25, type >25 in the Unit Price column. Click Apply Filter to review the records of products that cost more than $25 and have more than 50 units in stock. Note that there is one product with a reorder level of 25. Click Filter By Form.

When you use OR criteria in a query, you put the criteria on separate rows. The equivalent technique in Filter By Form is entering criteria on different tabs. At the bottom of the window, click the OR tab. Access gives it the focus and creates another OR tab, which you can use if you need more OR criteria in the future.

Open the list in the Reorder Level column and choose 0. Click Apply Filter. Note that all the products have a reorder level of 0, except for the one product that costs more than $25 and has 50 units in stock.

Save Filter As Query

Now we’ll do three things: (1) Save the filter as a query; (2) close the table; and (3) view the query. Click Filter By Form. Click File and Save As Query. Type Smart Computing Filter By Form and click OK. Click Clear Grid and click Apply Filter to view the unfiltered table. Close the table and click No when Access asks you if you want the design changes saved.

In the Database Window, click Queries and choose Smart Computing Filter By Form. Click Design. The Unit Price and Units In Stock columns in the Design Grid include the criteria you designated on the same row in Filter By Form. The Reorder Level criterion is on a different row. Click View to see the records that match those you saw in the table. Close the query. Remember to delete the query if you don’t want to keep it.