Access Hacks

Hack #52. Get Cleaner And-Based Criteria

Remove the need for multiple And statements by combining the In and Not operators.

Sometimes, criteria are set up to filter out certain records instead of including them. This reversal of logic makes sense in situations in which you want to return most of the records, but not all of them. "Get Cleaner Or-Based Criteria" [Hack #51] shows how to use the In operator to better manage Or based criteria. When you set up criteria to be excluded, however, use the And operator. For example, you might ask, "Give me all states, except California and New Mexico."

Figure 5-48 shows a query design that excludes six states from the query results. As new states are added to this list, an additional And operator is required. Eventually, this method of writing multiple Andoperators becomes tiresome and you end up having to scroll to read through it all.

Figure 5-48.  Using multiple And operators to filter out records

The In operator might come to mind as a way to reduce the long criteria statement. However, the point is to not include the criteria. The solution is to use both the In and the Not operators. Not is a logical operator: it reverses a condition. Including it with an In operator results in a list of items not to include, which works perfectly for this type of query.

Figure 5-49 shows the improved query, in which the multiple And statements are ...