Chapter 5: Using Operators and Expressions in Access
195
This is, of course, the opposite use of quotation marks as the previous example. In this case, Access
expects to find single quotes around “Bobby” in the first name field, and no records will be
returned.
The Like operator and wildcards
In previous sections, you worked with literal criteria. You specified the exact field contents for
Access to find, which was NY in the previous example. Access used the literal to retrieve the
records. Sometimes, however, you know only a part of the field contents, or you might want to see
a wider range of records on the basis of a pattern.
For example, you might want to see all product information for items with “convertible” in the
description. Many different makes and models may be convertibles, and there’s no field where
“convertible” will work by itself as the query’s criteria. You’ll need to use wildcards to make sure
you successfully select all records containing “convertible” in the description.
Here’s another example: Suppose you have a buyer who has purchased a couple of red models in
the last year. You remember making a note of it in the Notes field about the color, but you don’t
remember which customer it was. To find these records, you’re required to use a wildcard search
against the Notes field in tblCustomers to find records that contain the word Red.
Use the Like operator in the Criteria cell of a field to perform wildcard searches against the field’s
contents. Access searches for a pattern in the field; you use the question mark (?) to represent a
single character or the asterisk (*) for several characters. In addition to? and *, Access uses three
other characters for wildcard searches. Table 5.1 lists the wildcards that the Like operator can
use.
The question mark (?) stands for any single character located in the same position as the question
mark in the example expression. An asterisk (*) stands for any number of characters in the same
position in which the asterisk is placed. The pound sign (#) stands for a single digit (0–9) found in
the position occupied by the pound sign. The brackets ([]) and the list they enclose stand for any
single character that matches any one character in the list located within the brackets. Finally, the
exclamation point (!) inside the brackets represents the Not operator for the list — that is, any
single character that does not match any character in the list.
These wildcards can be used alone or in conjunction with each other. They can even be used mul-
tiple times within the same expression.
To create an example using the Like operator, let’s suppose you want to find the customer who
likes red model cars. You know that Red is used in one of the Notes field in tblCustomers. To
create the query, follow these steps:
- Add tblCustomers, tblSales, tblSalesLineItems, and tblProducts to the
query.
- Add Company and Notes from tblCustomers, SalesDate from tblSales, and
Description from tblProducts to the QBE pane.
