Match all characters anywhere in your data
-
Open your query in Design view. To do so, in the Navigation pane, under Queries, right-click the query and click Design View.
-
In the Criteria cell under the field you want to use, add an asterisk on either side of your criteria, or on both sides. For example:
"*owner*".
"owner*".
"*owner".
-
On the Query Design tab, in the Results group, click Run.
Match a character within a pattern
-
Open your query in Design view.
-
In the Criteria cell of the field you want to use, type the operator Like in front of your criteria.
-
Replace one or more characters in the criteria with a wildcard character. For example, Like R?308021 returns RA308021, RB308021, and so on.
-
On the Query Design tab, in the Results group, click Run.
Retrieve a list of companies from A through H
-
Open your query in Design view.
-
In the Criteria cell of the field you want to use, enter Like, followed by a pair of double quotes. For example: Like "".
-
Within the double quotes, add a pair of square brackets and the range of characters you want to find, like so:
Like "[a-h]"
-
You can use wildcards outside the brackets. For example:
Like "[a-h]*"
Table of wildcard characters
This table lists and describes the wildcard characters you can use in an Access query.
Symbol |
Description |
Example |
* |
Matches zero or more characters. It can be used as the first or last character in the character string. |
wh* finds wh, what, white, and why |
? |
Matches any single alphabetic character. |
b?ll finds ball, bell, and bill |
[ ] |
Matches any single character within the brackets. |
b[ae]ll finds ball and bell but not bill |
! |
Matches any character not in the brackets. |
b[!ae]ll finds bill and bull but not ball or bell |
- |
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, and 123 |
Examples of using wildcards
Situation |
Example |
---|---|
Your data lists some people as "owner" and others as "owner/operator". |
Like "owner*" or |
You want to use wildcards with a parameter query. |
Like "*" & [parameter] & "*" For example: Like "*" & [fish] & "*" Returns all recipes containing "fish", such as fish & chips, rockfish, and so on. |
Someone misspelled a name when they entered data, such as "Adrien" instead of "Adrian". |
Like "Adri?n" |
You want to find customers whose last name starts with A through H — for use in a bulk mailing, for example. |
Like "[a-h]*" |
You want to find part numbers that have all but the second and third digit in common. |
Like "R??083930" |
You want to send out invitations for a block party on the 1000 block of Park Street. |
Like "1### Park Street" |
Syntax to retrieve wildcard characters from your data
Character |
Required Syntax |
---|---|
Asterisk * |
[*] |
Question mark ? |
[?] |
Number sign # |
[#] |
Hyphen - |
[-] |
Set of opening and closing brackets together [] |
[[]] |
Opening bracket [ |
[[] |
Closing bracket ] |
No special treatment needed |
Exclamation point ! |
No special treatment needed |
Understanding which set of wildcards to use
Access database engine (ANSI-89) versus SQL Server (ANSI-92)
ANSI-89 describes the traditional Access SQL syntax, which is the default for Access databases. The wildcard characters conform to the Microsoft Visual Basic® for Applications (VBA) specification, not SQL.
ANSI-92 is used when you want your syntax to be compliant with a Microsoft SQL Server™ database.
It's recommended that you don't mix the two types of wildcards in the same database.
Wildcards for use with the Access database engines (ANSI-89)
Use these wildcard characters in queries created for an Access database.
Symbol |
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 |
? |
Matches any single alphabetic character. |
B?ll finds ball, bell, and bill |
[ ] |
Matches any single character within the brackets. |
B[ae]ll finds ball and bell but not bill |
! |
Matches any character not in the brackets. |
b[!ae]ll finds bill and bull but not ball or bell |
- |
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, and 123 |
Wildcards for use with SQL Server (ANSI-92)
Use these wildcard characters in queries created for use with a Microsoft SQL Server™ database.
Symbol |
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 |
_ |
Matches any single alphabetic character. |
B?ll finds ball, bell, and bill |
[ ] |
Matches any single character within the brackets. |
B[ae]ll finds ball and bell but not bill |
^ |
Matches any character not in the brackets. |
b[!ae]ll finds bill and bull but not ball or bell |
- |
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 |