Ms Office Tune Up

Mar 06

Building filter expressions in Access 2007

The secret to a good query’s getting the information you want, and nothing more. To tell Access what records it should get (and which ones it should ignore), you need a filter expression.

The filter expression defines the records you’re interested in. If you want to find all the orders that were placed by a customer with the ID 1032, you could use this filter expression:

=1032

To put this filter expression into action, you need to put it in the Criteria box under the CustomerID field.

Technically, you could just write 1032 instead of =1032, but it’s better to stick to the second form, because that’s the pattern you’ll use for more advanced filter expressions. It starts with the operator (in this case, the equals sign) that defines how Access should compare the information, followed by the value (in this case, 1032) you want to use to make the comparison.

WORD TO THE WISE
Don’t Get Confused by Lookups

As you know, lookups change the way values appear on the datasheet. If you add a lookup on the CustomerID field in the Orders table, then you don’t see a cryptic number like 1032. Instead, you see some descriptive information, like the name Hancock, John.

However, when you write your filter expression, you need to remember what information’s actually stored in the field. So the CustomerID filter expression =1032 works fine, but =Hancock, John doesn’t, because the name information’s actually stored separately. (It’s in the Customers table, not the Orders table.)

Sometimes, you really do want to create a filter expression that uses linked information. You may want to find records in the Orders table using a customer name instead of a customer ID, because you don’t have the ID value handy. In this situation, you have two choices:

You can look up the ID value you need in the Customers table before you start. Then, you can use that value when you build your query for the Orders table.
You can use a join query to get the name information from the Customers table, and display it alongside the rest of your order details. You’ll learn how to perform this trick in Section 6.3.

If you’re matching text, then you need to include quotation marks around your value. Otherwise, Access wonders where the text starts and stops:

=”Harrington Red”

Instead of using an exact match, you can use a range. Add this filter expression to the OrderTotal field to find all the orders worth between $10 and $50:

<50 And >10

This condition’s actually two conditions (less than 50 and greater than 10), which are yoked together by the powerful And keyword (Section 4.3.2.4). Alternatively, you can use the Or keyword if you want to see results that meet any one of the conditions you’ve included (Section 4.3.2.4).

Date expressions are particularly useful. Just remember to bracket any hardcoded dates with the # character (Section 4.3.2.2). If you add this filter condition to the DatePlaced field, then it finds all the orders that were placed in 2007:

<#1/1/2008# And >#12/31/2006#

This expression works by requiring that dates are earlier than January 1, 2008, but later than December 31, 2006.

Tip: With a little more work, you could craft a filter expression that gets the orders from the first three months of the current year, no matter what year it is. This trick requires the use of the functions Access provides for dates. See Section 4.1.2 for more details.

UP TO SPEED
Filter Syntax

If filters seem uncannily familiar, there’s a reason. Filters have exactly the same syntax as the validation rules you used to protect a table from bad data (Section 4.3). The only difference is the way Access interprets the condition. A validation rule like <50 And >10 tells Access a value shouldn’t be allowed unless it falls in the desired range (10 to 50). But if you pop the same rule into a filter condition, it tells Access you aren’t interested in seeing the record unless it fits the range. Thanks to this similarity, you can use all the validation rules you saw in Section 4.3.2, Section 4.3.2, Section 4.3.2.2, Section 4.3.2.3, Section 4.3.2.4, Section 4.3.3 to Section 4.4 as filter conditions.