Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

181

Table 5.1 shows the five wildcards you can use with the Like operator:

TABLE 5.1

Wildcards Used by the Like Operator

Wildcard Purpose

? A single character (0–9, Aa–Zz)

* Any number of characters (0–n)

# Any single digit (0–9)

[list] Any single character in the list

[!list] Any single character not in the list

Both [list] and [!list] can use the hyphen between two characters to signify a range.

Here are some wildcard examples:

[tblContacts].[LastName] Like “Mc*” Returns True for any last name that begins

with “Mc” or “MC,” such as “McDonald,”

“McJamison,” and “MCWilliams.” Anything

that doesn’t start with “Mc” or “MC” returns

False.

[Answer] Like “[A-D]” Returns True if the Answer is A, B, C, D, a,

b, c, or d. Any other character returns

False.

“AB1989” Like “AB####” Returns True because the string begins

with “AB” and is followed by four digits.

[LastName] Not Like “[A,E,I,O,U]*” Returns True for any last name that does

not begin with a vowel. “Smith” and

“Jones” return True while “Adams” and

“O’Malley” return False.

[City] Like “?????” Returns True for any city that is exactly five

characters long.

Tip

If the pattern you’re trying to match contains a wildcard character, you must enclose the wildcard character

in brackets. In the example:

“AB*Co” Like “AB[*]C*”

The [*] in the pattern treats asterisks in the third position as data. Since the asterisk character is enclosed

in brackets, it won’t be mistaken for a asterisk wildcard character.

Using wildcards