In part 1 of this 3-part 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. |
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). |
B[ ae ] ll finds ball and bell but not bill |
|
! |
Used inside brackets along with other characters. |
b[! ae ] ll finds bill and bull but not ball or bell |
|
- |
Used inside brackets along with other characters. |
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. |
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. |
B[ ae ] ll finds ball and bell but not bill |
|
^ |
Used inside brackets along with other characters. |
b[^ ae ] ll finds bill and bull but not ball or bell |
|
- |
Used inside brackets along with other characters. |
b[a-c]d finds bad, bbd |
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 adds 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.
Next up in part 3 of this 3-part series: Prompting for input by using a parameter.
