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
|
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.
|
|
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.
|
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
in the
Units On Order
field.
|
|
2.
|
Choose Filter Excluding Selection.
Any records with
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).
|
|
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).
|
|
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.
|
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.
Only products with purchase prices above 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).
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).
|
|
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.
|
