Part I: Access Building Blocks
198
TABLE 5.6
Using Wildcards with the Like Operator
Expression Field Used In Results of Criteria
Like “Ca*” tblCustomers.LastName Finds all records of contacts whose last
name begin with Ca (for example, Carson
and Casey).
Like “* red *” tblProducts.Features Finds all records of products with the
word red anywhere within the
Features field.
Like “C*” tblSales.PaymentMethod Finds all sales that were paid for by check
or credit card.
Like “9/*/2012” tblSales.SaleDate Finds all records of sales for the month of
September 2012.
Like “## South Main” tblCustomers.Address Finds all records of contacts with houses
containing house numbers between 10
and 99 inclusively (for example, 10, 22,
33, 51 on South Main).
Like “[CDF]*” tblCustomers.City Finds all records of contacts for customers
who live in any city with a name begin-
ning with C, D, or F.
Like “[!EFG]*” tblCustomers.City Finds all records of contacts who do not
live in any city that begins with the letters
E, F, or G; all other city records are
displayed.
Table 5.6 shows several examples that can be used to search records in the tables of the Collectible
Mini Cars database.
Specifying non-matching values
To specify a non-matching value, you simply use either the Not or the <> operator in front of the
expression that you don’t want to match. For example, you might want to see all contacts who
have purchased a vehicle, but you want to exclude buyers from New York state. Follow these steps
to see how to specify this non-matching value:
- Open a new query in Design view, and add tblCustomers.
- Add Company and State from tblCustomers.
- Click in the Criteria cell of State.
