Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

203

FIGURE 5.11

Using the Or cell of the QBE pane. You can place criteria vertically in the QBE grid.

Tip
Access allows up to nine Or cells for each field. If you need to specify more Or conditions, use the Or operator
between conditions (for example, AZ Or CA Or NY Or PA).

Access rearranges the design shown in Figure 5.11 when the query is saved to match the query in

Figure 5.10. In fact, when you open qryFigure_5-11 in the Chapter05.accdb example

database, you’ll see that it is exactly the same as qryFigure_5-10 because of the way Access

rearranged the criteria when qryFigure_5-11 was originally saved. When you build a query

using “vertical” Or criteria, Access optimizes the SQL statement behind the query by placing all the

Or criteria into a single expression.

Note
Access doesn’t allow periods in the names of objects, so I’ve used hyphens instead.

Using a list of values with the In operator

Another method for specifying multiple values of a single field is using the In operator. The In

operator finds a value from a list of values. For example, use the expression IN(AZ, CA, NY)

under the State field in the query used in Figure 5.11. The list of values in the parentheses

becomes an example criterion. Your query should resemble the query shown in Figure 5.12.

Access automatically adds quotation marks around AZ, CA, and NY.

Note
When you work with the In operator, each value (example data) must be separated from the others by a comma.