Back
Access

Query Criteria Part 2: Like what?

In part 1 of this series, we considered a basic example of using criteria in a query to make the query’s question more specific. That’s great if you know exactly what you’re looking for. But what if you want to see inexact matches – say, everyone whose last name starts with Th? You’re in luck – Access has powerful tools you can use in query criteria to retrieve inexact matches: wildcard characters and the LIKE operator.

Wildcard characters

If you’ve ever played poker, you may be able to guess what wildcard characters do: they can be substituted for other characters. There are ten wildcard characters, but only five can be used in any given database. Five of them meet a standard known as ANSI-89, and the other five meet the ANSI-92 standard; every database supports one standard or the other, but not both. You can set an option to specify which ANSI standard to use.

ANSI-89 characters

Character   

Description

Example

*

Matches any number of characters.
You can use the asterisk anywhere in a character string.

wh* finds what, white, and why, but not awhile or watch.

?

Matches any single alphabetic character.

B?ll finds ball, bell, and bill

[ ]

Used with other characters (enclosed within the brackets).
Matches any single character within the brackets.

B[ae]ll finds ball and bell but not bill

!

Used inside brackets along with other characters.
Matches any character not in the brackets.

b[!ae]ll finds bill and bull but not ball or bell

-

Used inside brackets along with other characters.
Matches any one of a range of characters.
You must specify the range in ascending order (A to Z, not Z to A).

b[a-c]d finds bad, bbd, and bcd

#

Matches any single numeric character.

1#3 finds 103, 113, 123

ANSI-92 characters

Character  

Description

Example

%

Matches any number of characters.
It can be used as the first or last character in the character string.

wh% finds what, white, and why, but not awhile or watch.

_

Matches any single alphabetic character.

B_ll finds ball, bell, and bill

[ ]

Used along with other characters.
Matches any single character within the brackets.

B[ae]ll finds ball and bell but not bill

^

Used inside brackets along with other characters.
Matches any character not in the brackets.

b[^ae]ll finds bill and bull but not ball or bell

-

Used inside brackets along with other characters.
Matches any one of a range of characters.
You must specify the range in ascending order (A to Z, not Z to A).

b[a-c]d finds bad, bbd, and bcd

The LIKE operator

Access requires the word “Like” in any criterion that uses wildcards. You use the LIKE operator immediately preceding the string that contains the wildcards. Access also needs the string surrounded by quote marks. For example, to match text data that contains the letter T you would use this expression:

LIKE “*t*”

In recent versions, Access will add the LIKE operator and quote marks for you if you omit them. So if you use the expression *t* Access changes it to Like “*t*” when you run the query or move the cursor outside of the criterion.

See LIKE and wildcard characters in action

If you’d like to watch a video demonstrating the use of these tools, try this YouTube video on Like and wildcards in Access query criteria.

Stay tuned for more about query criteria

Next up in part 3 of this series: using query parameters to make query criteria even more flexible.

–Steven Thomas