The reason you might want to consider using a filter is to display specific records in an open datasheet or form. All of the records remain intact, and all become visible again once you remove the filter. There are several ways to create a filter, including Filter By Selection, Filter Excluding Selection, Filter By Input, and Filter By Form. The last of these, Filter By Form, is by far the most robust and makes it easy to apply AND and OR criteria (see our "Microsoft Access 2002: Learn To Use Filter By Form" article from December 2003). Perhaps the most powerful filtering method, however, is Advanced Filter/Sort. This technique uses the same design grid that appears in the query design view but has the same Filter/Sort toolbar as Filter By Form. Our tutorial will show you the advantages and disadvantages of Advanced Filter/Sort, particularly with respect to Filter By Form. In addition, we’ll review all filter methods.

 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 now open and use without worrying about changing the original file. If you prefer to work with the original Northwind database, launch Access and click Help, Sample Databases, and Northwind Sample Database.

Open the Orders table to get a feel for its data. The last field is Ship Country, so let’s assume you want to view only orders shipped to France. Right-click any occurrence of France and choose Filter By Selection. (You also could right-click anywhere in the Ship Country column, click inside the Filter For field, type France, and press ENTER.) Only orders sent to France are now in view.

Let’s exclude any orders to France destined for the city of Marseille. In the Ship City field, find any value of Marseille, right-click it, and choose Filter Excluding Selection. By doing this, the filter now excludes all orders to Marseille.

Suppose you’d also like to see anyorder shipped in 1998 or afterward. To do this, you’ll use OR criteria, for which you need either Filter By Form or Advanced Filter/Sort. Let’s add the condition in Advanced Filter/Sort and compare the results in Filter By Form. Click Records, Filter, and Advanced Filter/Sort.

If you’ve created queries before, this window will look familiar. The Orders field list is in view, from which you can add fields to the design grid. Because of our earlier work, Access added the Ship Country field to the grid and France (a literal value which Access encloses in quotes) is in the Criteria row. The grid also includes the Ship City field, and lists Marseille in its Criteria row, preceded by <> (an operator which means to exclude).

Double-click ShippedDate from the Orders field list to add it to the grid. In the Or row, type >12/31/97, which will give us all orders shipped in 1998 and later. Click the Apply Filter button. Although records from 1996 and 1997 only include orders shipped to France (excluding Marseille), all orders shipped in 1998 will display.

The records are currently sorted by OrderID. Let’s sort them by Shipped Date in descending order (with the most recent orders listed first). Select the Shipped Date field and click Sort Descending (the Z-A button). Choose Records, Filter, and Advanced Filter/Sort. The ShippedDate field is now in the first column, and Descending is listed in its Sort field.

Next, choose Filter By Form from the Filter menu. Scroll to view the criteria in the Ship City and Ship Country fields. Click the Or tab to see the OR criteria you added to the Shipped Date field. At first glance, Advanced Filter/Sort seems to have the more useful, compact, and informative interface, but we also should point out situations that prove otherwise.
.

 Drawbacks Of Advanced Filter/Sort

Let’s use Advanced Filter/Sort with some different criteria to see another side of Advanced Filter/Sort. Click the Apply Filter button to return to datasheet view, and then click the Remove Filter button. Select the Order ID column and click Sort Ascending (the A-Z button). The Record Selector bar should indicate there are 830 records.

Suppose you want to see only orders shipped by Speedy Express, excluding those executed by employee Janet Leverling. Scroll to the Ship Via column, right-click any occurrence of Speedy Express, and choose Filter By Selection. Then, scroll to the Employee column, right-click any occurrence of Janet Leverling, and choose Filter Excluding Selection. Choose Records, Filter, and Advanced Filter/Sort.

The design view now has two more field lists and several crisscrossed lines (see Figure 2). Access added the two extra field lists because Ship Via and Employee are what Access refers to as lookup fields.

You may or may not understand lookup fields, and you may or may not feel comfortable with the three field lists, but there’s no doubt that Advanced Filter/Sort introduces more complexity to filter design. Click Filter, and click Filter By Form. As you’ll notice, even though the criteria have changed, the same familiar interface remains. Designating criteria in lookup fields doesn’t add any complexity in Filter By Form.

The upshot is that Advanced Filter/Sort offers the most versatility of all filter methods. Even so, remember that Advanced Filter/Sort sometimes requires a higher level of Access knowledge.