Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

189

FIGURE 5.1

The QBE pane shows a simple criterion asking for all models in the Cars category.

Understanding complex criteria

You build complex query criteria using any combination of the operators shown in Table 5.5. For

many queries, complex criteria consist of a series of Ands and Ors, as in these examples:

l State must be Connecticut or Texas.

l (^) City must be Sunnyville and state must be Georgia.
l State must be MA or MO and city must be Springfield.
These examples demonstrate the use of both logical operators: And/Or. Many times, you can create
complex criteria by entering example data in different cells of the QBE pane, as shown in Figure
5.2. In Figure 5.2, criteria is specified in both the State and Category columns. Within the State
column, the criteria specifies “either California or Arizona,” while the additional criteria in the
Category column adds “not Cars.” When combined, the criteria in the two columns limits the
returned records to those where the customer state is either California or Arizona, and the product
category is not cars.
However, using explicit Boolean operators is not the only way to select records based on multiple
criteria. Figure 5.3 demonstrates a common Access technique using complex criteria without
entering the operator keywords And/Or at all. In this example, the criteria “stacked” within a single
column specifies Or. For example, in the State column, the criteria is interpreted as “CA” Or
“AZ”. The presence of criteria in another column in the QBE grid implies And. Therefore, the cri-
teria in the Category column is combined with the state criteria is interpreted as:
(State = “CA” And Category <> “Cars”) Or (State = “AZ” And Category