Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
To pinpoint a distinct item once you forget how it is spelled, consider using a wildcard character in a query.
Wildcards are unique characters that can replace unfamiliar characters in a text value and are resourceful for discovering several items with related, but not equivalent data. Wildcards are even useful for acquiring data according to a tailored pattern match. For example, capturing everyone named John on Park Street.
For more information about queries, see introduction to queries.
Here are some examples of wildcard characters for Access queries:
|*||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 a single alphabet in a specific position.||b?ll finds ball, bell, and bill.|
|[ ]||Matches characters within the brackets.||b[ae]ll finds ball and bell, but not bill.|
|!||Excludes characters inside the brackets.||b[!ae]ll finds bill and bull, but not ball or bell.Like “[!a]*” finds all items that do not begin with the letter a.|
|–||Matches a range of characters. Remember to specify the characters 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.|
Learn more about applying criteria to a query.
Examples of wildcard character pattern matching in expressions
To utilise a wildcard character among a pattern:
- Enter your query in Design view.
- In the Criteria row of the field that you want to use, enter the operator Like in front of your criteria.
- Substitute one or greater characters in the criteria with a wildcard character. For example, Like R?308021 returns RA308021, RB308021, and so on.
- On the Design tab, choose Run.
Listed below are some examples of wildcard patterns that you can employ in expressions:
|Character(s)||Use to match|
|? or _ (underscore)||Any single character.|
|* or %||Zero or more characters.|
|#||Any single digit (0 — 9).|
|[charlist]||Any single character in charlist.|
|[!charlist]||Any single character not in charlist.|
|[a-zA-Z0-9].||Any alphanumeric character.|
|[A-Z]||Any of the uppercase letters in the range A through Z.Note: When you specify a range of characters, the characters must appear in ascending sort. For example, [Z-A] is not a valid pattern.|
Check out the basics of building an expression.
To link special characters like question mark (?), number sign (#), and asterisk (*), enclose them in square brackets.
The CHARLIST function gathers matches for one or greater characters and can encompass nearly any characters in the ANSI character set, even digits. The CHARLIST is wrapped in brackets ([ ]) and can be applied with wildcard characters for more precise matches.
To single out a range of characters, apply CHARLIST with a hyphen (-) to split the upper and lower parameters of the range.
To connect the hyphen (-) character, place it at the beginning or end of CHARLIST (after the exclamation mark if you’re using one). In any other position, the hyphen ascertains a collection of ANSI characters.