You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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.
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.
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
%
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.
_
B_ll finds ball, bell, and bill
Used along with other characters. Matches any single character within 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).
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.
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 series: using query parameters to make query criteria even more flexible.
--Steven Thomas
Comments: (2) Collapse
Microsoft Access queries are awesome. Everyone should know how to use them well to maximize the value of Access.
For additional information on setting query criteria and a variety of other techniques to maximize the value of Access queries, take a look at my paper on Microsoft Access Query Tips and Techniques (SQL and VBA)
www.fmsinc.com/.../index.html
Our Microsoft Access Query Help Center: www.fmsinc.com/.../help-center.html
offers even more resources for learning about and using queries.
Thanks Luke! That's an excellent resource.
-Steven
Comments: (loading) Collapse