Locating Information that Matches Multiple Criteria

Filter By Selection, Filter For <input>, and Filter By Form are quick and easy ways to hone in on the information you need, as long as your filter criteria are fairly simple. But suppose The Garden Company needs to locate all the orders shipped to Midwest states between specific dates by either of two shippers. When you need to search a single table for records that meet multiple criteria or that require complex expressions as criteria, you can use the Advanced Filter/Sort command.

You work with the Advanced Filter/Sort command in the design grid. You can use this design grid to work with only one table.

Tip  

If you create a simple query in the filter window that you think you might like to use again, you can save it as a query. Either click Save As Query on the File menu; click the Save As Query button on the toolbar; or right-click in the filter window, and then on the shortcut menu, click Save As Query.

In this exercise, you will create a filter to locate customers in two states using the Advanced Filter/Sort command. After locating the customers, you will experiment a bit with the design grid to get a better understanding of its filtering capabilities.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\Queries\AdvFilter folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

1. Click Tables on the Objects bar, and double-click Customers to open the Customers table in Datasheet view.

2. On the Records menu, point to Filter , and then click Advanced Filter/Sort .

   Tip  

   Remember, if you don t see the command on the menu, you can hover over a short menu to display the long menu, or click the double-chevrons at the bottom of the menu.

   Access opens the filter window with the Customers field list in the top area.

3. If the design grid is not blank, on the Edit menu, click Clear Grid .

4. Double-click LastName to copy it to the Field cell in the first column of the design grid.

5. Click in the Criteria cell under LastName , type s* , and press [ENTER].

   Access changes the criterion to Like s* .

6. Scroll to the bottom of the Customers field list, and double-click Region to copy it to the next available column of the design grid.

7. Click in the Criteria cell under Region , type ca or wa , and press [ENTER].

   

   Your entry has changed to ca Or wa . The filter will now match customers with a last name beginning with s who live in California or Washington.

8. On the Filter menu, click Apply Filter/Sort to view the records that match the criteria.

   

   Tip  

   You can keep an eye on both the filter window and the table window if you reduce both in size .

9. On the Records menu, click Filter and then Advanced Filter/Sort to return to the filter window.

10. Click in the or cell in the LastName column, type b* , and press [ENTER].

    

11. On the Filter menu, click Apply Filter/Sort .

    The result includes records for all customers with last names that begin with s or b , but some of the b names live in Montana and Oregon. If you look again at the design grid, you can see that the filter is formed by combining the fields in the Criteria row with the And operator, combining the fields in the Or row with the And operator, and then using the Or operator to combine the two rows. So the filter is searching for customers with names beginning with s who live in California or Washington, or customers with names beginning with b , regardless of where they live.

12. Return to the filter window, type ca or wa in the or cell under Region , and press [ENTER].

13. Apply the filter again to see only customers from California and Washington.

14. Close the Customers table without saving your changes.

CLOSE the GardenCo database.

Expressions

The word expressions , as used in Access, is synonymous with formulas . An expression is a combination of operators , constants , functions , and control properties that evaluates to a single value. Access builds formulas using the format a=b+c , where a is the result and =b+c is the expression. An expression can be used to assign properties to tables or forms, to determine values in fields or reports , as part of queries, and in many other places.

The expressions you use in Access combine multiple criteria to define a set of conditions that a record must meet before Access will select it as the result of a filter or query. Multiple criteria are combined using logical, comparison, and arithmetic operators. Different types of expressions use different operators.

The most common logical operators are And , Or , and Not . When criteria are combined using the And operator, a record is selected only if it meets them all. When criteria are combined using the Or operator, a record is selected if it meets any one of them. The Not operator selects all records that don t match its criterion.

Common comparison operators include < (less than), > (greater than), and = (equal). These basic operators can be combined to form <= (less than or equal to), >= (greater than or equal to), and <> (not equal to). The Like operator is sometimes grouped with the comparison operators and is used to test whether or not text matches a pattern.

The common arithmetic operators are + (add), - (subtract), * (multiply), and / (divide), which are used with numerals. A related operator, & (a text form of +) is used to concatenate ”or put together ”two text strings.