Microsoft Access 2010 Bible

Part I: Access Building Blocks

196

3. Click the Criteria cell of the Notes field and enter red as the criteria.

Be sure to put a space between the first asterisk and the r and the last asterisk and the
d — in other words, put spaces before and after the word red.

Tip
In the preceding steps, you put a space before and after the word red. If you didn’t, Access would find all
words that have the word red in them — like aired, bored, credo, fired, geared, restored, and on and on. By
placing a space before and after the word red, Access is being told to look for the word red only.

There is, however, one issue with this example. Notice that the criteria (“* red *”) requires a

space after the word red. This means that a record containing the following note will not be

returned by this query:

Customer wants any model of car, as long as it’s red!

Because there is no space immediately after red, this record will be missed. The proper criteria to

use is

Like “* red[ ,.!?]”

The brackets around “ ,.!?” instruct Access to select records when the Notes field ends with the

word red, followed by a space or punctuation character. Obviously, there may be other characters

to consider within the brackets, and you must have a good idea of the variety of data in the queried

field.

When you click outside the Criteria cell, Access automatically adds the Like operator and the

quotation marks around the expression. Your query QBE pane should look like Figure 5.6.

FIGURE 5.6

Using the Like operator in a select query