Use wildcards in queries and parameters in Access

Applies To
Access for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Match all characters anywhere in your data

  1. Open your query in Design view. In the Navigation Pane, under Queries, right-click the query and select Design View.

  2. In the Criteria cell under the field that you want to use, add an asterisk on one side of your criteria or on both sides. For example:

    • *owner*
    • owner*
    • *owner

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

    Run button

Match a character within a pattern

  1. Open your query in Design view.
  2. In the Criteria cell of the field that you want to use, type the Like operator before your criteria.
  3. Replace one or more characters in the criteria with a wildcard character. For example, Like "R?308021" returns RA308021, RB308021, and similar values.
  4. On the Query 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 that 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 and rockfish.
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 example, for a bulk mailing. 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 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 follow the Microsoft Visual Basic for Applications (VBA) specification, not SQL.

ANSI-92 is used when you want your syntax to comply 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