Filters

You filter a table, form, or query by applying criteria to view a selection of data or, more formally, a subset of the records. There are several filtering methods, including Filter by Selection, Filter Excluding Selection, Filter For Input, Filter by Form, and Advanced Filter/Sort. Different filtering tools can be accessed from either the Filter submenu on the Records menu, the (right-click) shortcut menu, or the toolbar. Table 7.2 summarizes these options.

Table 7.2. Filter Selection Methods

Method	Records, Filter Menu	Shortcut Menu	Toolbar Button
By Selection	Yes	Yes	Yes
Excluding Selection	Yes	Yes	No
Filter for Input	No	Yes	No
Filter by Form	Yes	No	Yes
Advanced Filter/Sort	Yes	No	No
Excluding Selection and Advanced Filter/Sort buttons can be added to the toolbar. Right-click it, choose Customize, and follow the directions in the Customize dialog box.

Each method has advantages and disadvantages. Moreover, these methods can be used in combination: You can start out using one method and switch to a second (or yet a third) to refine the filter. The extended exercise in using filters that follows uses the Merchandise form, which has the same fields as the Merchandise table. Spliced among the many steps are a few comments about each method and how it works. Filtering is not a difficult topic, but some aspects are not self-evident, as you'll see presently.

NOTE

At this point, it would be ideal to introduce expressions, which are essential for creating filters and queries. But I believe you'll gain a better appreciation of expressions if you work with filters first to see how expressions are used. I think you'll grasp how the operators known as AND and OR work from my brief descriptions. But if you have problems, see my discussion of these operators later in the chapter.

Filter by Selection

To get a filter by selection example under your belt, follow these steps:

1.	Open the frmMerchandise form, which is in Datasheet view. Maximize the window. Briefly review the data.
2.	In the Category field, find any value of Decorative. Right-click the value and choose Filter by Selection. You have filtered for records with Decorative in the Category field (see Figure 7.2). Only those nine records are now displayed. Figure 7.2. You can use Filter by Selection to quickly filter your records for a single valuein this case, Decorative.
3.	In the Supplier field, right-click any instance of Lyons and Taigras and choose Filter by Selection. You have further filtered the nine Decorative products records to display only those supplied by Lyons and Taigras.

NOTE

The Filter by Selection and Filter by Form buttons are on the middle of the toolbar. To their right is Apply/Remove Filter, a toggle button whose function switches when the filter is applied or removed (see Figure 7.2). The Records menu and the Records, Filter submenu together contain these and all other filter commands.

Although these examples correctly indicate the ease of using Filter by Selection, this method actually works in quite specific ways:

• If you select the entire contents of a field, Access filters for only that exact match. Thus, a filter for Ann displays only records with Ann; it does not display records with Anne, Ann Marie, or Mary Ann.

• If you select the first word in a two-word field, Access finds records in which the text is a match at the start of the field. If you select East in East New York, for example, the filtered records will include East Huntington and Eastern Montana, but not Hudson East or Heastville. The criterion for the filter is east*. Similarly, filtering for East in Hudson East (where the text string east is at the end) displays Hamptons East and Orlando Feast but not East Kansas or Peasterville. The criterion for the filter is *east.

• If you make a selection of any text string at the beginning of the field, Access displays only values that have that text string at the beginning of the field. Thus, if you highlight and filter for Bake in Bakersfield, Access displays records with Bakerstown but not Andenbake or Andenbaker. Similarly, filtering for bake in Andenbake finds records with Littenbake but not Bakersfield or Andenbaker.

• If you select a text string in the middle of the field, Access displays any value with that string, regardless of the position in the text string in the value. Thus, if you filter for land in Orlando, Access displays records with Lands End, Greenland, and Netherlands. The criterion is *land*, with wildcards at both the beginning and end of the text string.

Q&A

Q1:

You said the Merchandise table has the same fields as the Merchandise form. But I just compared the two. In the table, the Category and Supplier columns have numbers, which are the CategoryID and SupplierID. In the form, the columns display text, the actual category and supplier names. I opened Tools, Relationships to refresh my memory of the database's relationships. I understand that the fields that have data about categories and suppliers are foreign keys in the Merchandise table. I understand that they are on the many side of one-to-many relationships with, respectively, the Categories and Suppliers tables. I understand that, because these relationships have been established, I can combine and integrate the data in these tables. It is certainly convenient and informative to have actual category and supplier names instead of meaningless ID numbers. But didn't you drag us through that long, tedious example at the beginning of Chapter 5 just to tell us not to use lookup fields? It seems to me that's exactly what you're doing here.

A1:

You're right, in that the principle is exactly the same: You're looking up values from the table on the "one" side of the relationship and displaying them in the foreign key from the related table. But remember, you're working in a form, not a table. The form's Datasheet view makes table and form appear as identical objects, but they have different purposes. You use the form to view and enter data, whereas you use tables to store data. For viewing and entering values, you want to look up non-ID values because a category of toys is far more meaningful than a CategoryID of 4. The tool in the form that enables you to look up values is a combo box. You learn how to create combo boxes in Chapter 11, "Forms/Subforms."

Filter Excluding Selection

Suppose you want to see all records except those containing a certain value. You can use Filter Excluding Selection:

1.	Right-click any value of 0 in the Units On Order field.
2.	Choose Filter Excluding Selection. Any records with 0 units on order are removed from the filter.

As you can see, Filter Excluding Selection works the same way as Filter by Selection, except that records with values that match the criteria are not displayed.

Filter by Form

Filter by Form provides a graphical interface for choosing filter criteria. Each field has a drop-down list that contains all its values (assuming that the list is 1,000 entries or less), which makes it easy to select criteria. You can also start typing criteria in the field, and Access will propose the most likely candidate by completing the value for you.

1.	Click Remove Filter.
2.	Click Filter by Form.
3.	Choose Edit, Clear Grid. This eliminates any existing criteria in the Filter by Form interface.
4.	Click in the Category field, open the drop-down list, and select Toys.
5.	Click in the Supplier field. Type s for Stewart Productions. Press Tab. Two conditions must now be true at the same time to have a match: The category must be Toys, and the supplier must be Stewart Productions. When two conditions must be present to have a match, you are using AND criteria (see Figure 7.3). Figure 7.3. The Filter by Form window with AND criteria.
6.	Click Apply Filter. Stewart Productions supplies two toys.

TIP

When you switch to the Filter by Form window, you will often see criteria on the grid. Don't rely on this display to accurately reflect the current filter, whether or not applied. As you'll see presently, you should use Advanced Filter/Sort instead.

Let's try another example with Filter by Form. Suppose you want to view all products made by Stewart Productions and all products made by Feline Fantastics.

1.	Click Filter by Form.
2.	Delete Toys in the Category field.
3.	Click the Or tab at the bottom of the screen (see Figure 7.4). Figure 7.4. Each time you add OR criteria in Filter by Form, an additional Or tab appears at the bottom of the screen. To add filters for additional suppliers, click the Or tab that's farthest right.
4.	Open the drop-down list in the Supplier field and choose Feline Fantastics.
5.	Click Apply Filter.

In this case you've used OR criteria. Products are displayed under two conditions. First, they must be supplied by Stewart Productions. Second, they must be supplied by Feline Fantastics. If either condition is true, the record is shown. A total of 10 products were provided by either Stewart Productions or Feline Fantastics.

Here's a final example:

1.	Click Filter by Form.
2.	On the Look For tab, open the drop-down list in the Category column and select Toys.
3.	Click Apply Filter.
4.	Review the records (see Figure 7.5). Note that there are toys from both Stewart Productions and Feline Fantastics, but there are also products from other categories made by Feline Fantastics. Figure 7.5. The record set includes only toys made by Stewart Productions but all products made by Feline Fantastics The Status Bar at the bottom of the screen tells you that the datasheet has been filtered and gives you the number of records in the filter.

In this filter, you've used both AND and OR criteria together. There are two sets of conditions. First, any toy made by Stewart Productions is displayed. The product both must be a toy and must be made by Stewart Productions, so you are using AND criteria.

Second, in addition to toys made by Stewart Productions, all products made by Feline Fantastics are displayed. Here you are using OR criteria. Either set of conditionsthat it's a toy made by Stewart Productions, or that it's a product made by Feline Fantasticscan be true to have a match.

Filter for Input

Filter for Input is useful when you can't immediately locate a value to use Filter by Selection, or you want to use an expression (such as >7, as used in the following example). You can also enter expressions in Filter by Form or Advanced Filter/Sort, but Filter for Input is fastest.

1.	Right-click anywhere in the Purchase Price field.
2.	Click in Filter For and type >7 (see Figure 7.6). Press Enter. Figure 7.6. You can enter expressions using Filter for Input. Only products with purchase prices above $7 are displayed.

When you enter a string of alphanumeric characters in Filter for Input, Access filters for only an exact match. For example, if you enter manager, only Manager will be found, not Accounting Manager, Manager of Operations, or Managerial Operations. You can use wildcards, however, so that shouldn't be a problem. Filtering for *manager* will give you all those hits and likely others as well.

Advanced Filter/Sort

Advanced Filter/Sort offers the greatest flexibility for creating filters and sorts. It's also extremely useful for diagnosing a wayward filter.

1.	Click Remove Filter.
2.	Click Filter by Form.
3.	Click Clear Grid (see Figure 7.3 if you can't find this button).
4.	Open the Suppliers drop-down list. Choose Lyons and Taigras.
5.	Click Apply Filter. No records are displayed. Assume that you have no idea why the filter didn't retrieve any records for supplier Lyons and Taigras. You want to see exactly which criteria Access searched for.
6.	Choose Records, Filter, Advanced Filter/Sort (see Figure 7.7). Figure 7.7. The Advanced Filter/Sort window provides a detailed look at the filter. This grid is similar to the design grid of the query. At top, you see the scheme that shows how Access finds supplier company names: It uses SupplierID, the field with common data, to "look up" the SuppCompanyName in the Suppliers table. But on the Criteria row, note that Access interpreted the criteria you selected as "Lyons" AND "Taigras"two separate valuesbecause and is a word reserved for AND criteria. You need to edit the criteria to find the supplier records you want. NOTE Interestingly, if you had selected any occurrence of Lyons and Taigras in the datasheet and filtered by selection, you wouldn't have had any problem. As noted earlier, when you choose the entire value in filter by selection, the match must be exact, and thus the criteria would be "Lyons and Taigras". In this case, Access would have interpreted the entire string as a literal value rather than as an expression. These terms are defined later in the chapter and in the glossary.
7.	Click in the Criteria row. Edit the criteria to "Lyons and Taigras".
8.	Click Apply Filter. The five records that match Lyons and Taigras are displayed. The Advanced Filter/Sort window is also useful for sorting on two fields when the two sorts are in opposite directions.
9.	Click Remove Filter.
10.	Click the Category column selector to select the field. Click the Ascending (AZ) button on the toolbar for an Ascending sort.
11.	Choose Records, Filter, Advanced Filter/Sort. You can see that the CategoryName column now has an ascending sort.
12.	Delete the SuppCompanyName column from the Advanced Filter grid.
13.	Double-click PurchasePrice from the tblMerchandise field list to add it to the grid. On the Sort row, type d in the PurchasePrice column for a descending sort that will sort products by highest price first (see Figure 7.8). Figure 7.8. In Advanced Filter/Sort, you can use the design grid to set sorts in two fields that go in opposite directions. (The field lists in the top pane have been lengthened and rearranged for a better view.)
14.	Click Apply Filter. The products are sorted first by category and then, within each category, by descending product price.
15.	Close the form and save your changes.

As the example makes clear, Advanced Filter/Sort offers the most versatility of all filter methods, and it also defines all aspects of the query. It's extremely useful for troubleshooting any filter problems, as well as creating more advanced filters.

NOTE

Chapter 8 discusses how you can save a filter as a query.