Microsoft Access 2010 Bible

Part I: Access Building Blocks

204

FIGURE 5.12

Using the In operator to find all records for buyer state being either AZ, CA, or NY

Using And to specify a range

The And operator is frequently used in fields that have numeric or date/time data types. It’s seldom

used with text data types, although it can be this way in some situations. For example, you might

be interested in viewing all buyers whose names start with the letters d, e, or f. The And operator

can be used here (> “Cz” And <“G”), although the Like operator is better (Like “[DEF]*”)

because it’s much easier to understand.

You use the And operator in queries when you want a field to meet two or more conditions that

you specify. For example, you might want to see records of buyers that have purchased products

between October 1, 2012, and March 31, 2013. In other words, the sale had to have occurred dur-

ing the last quarter of the year 2012 and the first quarter of 2013. The general expression for this

example is

(SaleDate >= 10/1/2012) And (SaleDate <= 3/31/2013)

Note
Parentheses are included in this example for clarity.

Unlike the Or operation (which has several conditions under which it is True), the And operation

is True only when both sides of the expression are True. To clarify use of the And operator, con-

sider these conditions:

l SaleDate (9/22/2012) is not greater than 10/01/2012 but is less than 3/31/2013 — the

result is False.

l SaleDate (4/11/2013) is greater than 10/01/2012 but is not less than 3/31/2013 — the

result is False.

l SaleDate (11/22/2012) is greater than 10/01/2012 and is less than 3/31/2013 — the

result is True.

Using an And operator with a single field sets a range of acceptable values in the field. Therefore,

the key purpose of an And operator in a single field is to define a range of records to be viewed.

For example, you can use the And operator to create a range criterion to display all buyers who