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. button image

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

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×