Microsoft Access 2010 Bible

Part I: Access Building Blocks

212

FIGURE 5.19

Using Ands and Ors in a select query

“Or”
because criteria
are in different rows

“And”

because criteria

are in the same row

A complex query on different lines

Suppose you want to view all records of Chevy models bought in the first six months of 2012

where the buyer lives in Massachusetts, or any type of vehicle from buyers in California. In this

example, you use three fields for setting criteria: tblCustomers.State, tblSales.

SaleDate, and tblProducts.Description. Here’s the expression for setting these criteria:

((tblSales.SaleDate Between #1/1/2012# And #6/30/2012#) And

(tblProducts.Description = Like “*Chev*” ) And (tblCustomers.

State = “MA”)) OR (tblCustomers.State = “CA”)

The query design is shown in Figure 5.20.

Note
You can enter the date 1/1/12 instead of 1/1/2012, and Access processes the query exactly the same. The
Regional and Language settings in the Windows Control Panel determine how dates are interpreted. By default,
two-digit years from 00 to 30 are interpreted as 2000 to 2030, while all two-digit years between 31 and 99 are
taken to be as 1931 to 1999. This is one reason why consistently using four-digit years during data entry is
always a good idea.