Chapter 5: Using Operators and Expressions in Access
209
expression for this example is (Criteria1 And Criteria2 And Criteria3) Or Criteria4
Or Criteria5. Therefore, this query displays a record if a value matches any of these criteria:l (^) Criteria1 And Criteria2 And Criteria3 (all must be True)
l Criteria4 (this can be True and either/both of the other two lines can be False)
l (^) Criteria5 (this can be True and either/both of the other two lines can be False)
As long as one of these three criteria is True, the record appears in the query’s results.
Here’s the SQL statement behind the query in Figure 5.16:
SELECT Table1.Field1, Table1.Field2,
Table1.Field3, Table1.Field4, Table1.Field15
FROM Table1
WHERE (Table1.Field1=“Criteria1”
AND Table1.Field2=“Criteria2”
AND Table1.Field3=“Criteria3”)
OR (Table1.Field4=“Criteria4”)
OR (Table1.Field5=“Criteria5”)
The locations of the parentheses in this SQL statement are significant. One set of parentheses sur-
rounds the criteria for Field1, Field2, and Field3, while parentheses surround each of the crite-
ria applied to Field4 and Field5. This means, of course, that Criteria1, Criteria2, and
Criteria3 are applied as a group, while Criteria4 and Criteria5 are included individually.
Specifying Or criteria across fields of a query
Although the Or operator isn’t used across fields as commonly as the And operator, occasionally
Or is very useful. For example, you might want to see records of any models bought by contacts in
Connecticut or you might want to see records on truck models, regardless of the state they live in.
To create this query, follow these steps:
- Add tblCustomers, tblSales, tblSalesLineItems, and tblProducts to a
new query. - Add Company and State from tblCustomers, and Description and Category
from tblProducts. - Enter CT as the criteria for State.
- Enter Trucks in the Or cell under Category.
Your query should resemble Figure 5.17. Notice that the criteria entered are not in the
same row of the QBE pane for State and Category. When you place criteria on differ-
ent rows in the QBE grid, Access interprets this as an Or between the fields. This query
returns customers who either live in Connecticut or who have bought truck models.