Use wildcards in queries and parameters in access

Match all characters anywhere in your data

1. Open your query in Design view. To do so, in the Navigation pane, under Queries, right-click the query and click Design View.

2. 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".

3. On the Design tab, in the Results group, click Run.

Match a character within a pattern

1. Open your query in Design view.

2. In the Criteria cell of the field you want to use, type the operator Like in front of your criteria.

3. Replace one or more characters in the criteria with a wildcard character. For example, Like R?308021 returns RA308021, RB308021, and so on.

4. On the Design tab, in the Results group, click Run.

Retrieve a list of companies from A through H

1. Open your query in Design view.

2. In the Criteria cell of the field you want to use, enter Like, followed by a pair of double quotes. For example: Like "".

3. Within the double quotes, add a pair of square brackets and the range of characters you want to find, like so:

   Like "[a-h]"

4. 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 Like "*owner*"
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

See also

• Access wildcard character reference

• Use the Find and Replace dialog box to change data

• Using the Like operator and wildcard characters in string comparisons

• Examples of query criteria

• Like Operator