Microsoft Access 2010 Bible

Part I: Access Building Blocks

194

FIGURE 5.5

The query design window showing tblCustomers open

You don’t have to enter an equal sign before the literal word NY because this is a select query. To

see all states except NY, you must enter either the <> (not equal) or the Not operator before NY.

You also don’t have to type quotes around the word NY. Access assumes that you’re using a literal

string NY and adds the quotes for you automatically.

Tip
If you type quotation marks, you should use the double quotation mark to surround literals. Access normally
uses the single quotation mark as a remark character in its programming language. However, when you use the
single quotation mark in the Criteria cell, Access interprets it as a double quotation mark.

Special considerations apply when data in the field contains quotation marks. For example, con-

sider a query to find a person whose name is given as Robert “Bobby” Jones. Ideally, the contacts

table would include a nickname field to capture “Bobby,” but, in the absence of a nickname field,

the data entry clerk may enter the first name as Robert “Bobby,” using the quotation marks around

“Bobby.”

In this case, Access sees the double-quotation characters as data, and you may want to include the

quotes in the criteria. The simplest solution is to use a criteria expression such as the following:

‘Robert “Bobby”’

Notice the single quotes surrounding the criteria string. Access correctly interprets the single

quotes as delimiting characters, and understands that the double quotes within the single quote are

just data. You should not use an expression such as the following:

“Robert ‘Bobby’”