Match all characters anywhere in your data
MS Query - Wildcard 'Like' with a parameter Hi there, This is my first post so appologies if it's in the wrong forum. I have an MS Query that downloads data from a SQL server. One of the fields contains (amonst other things) an agreement number and I want to be able to select the agreement number at run time.
- 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 Design tab, in the Results group, click Run.
Match a character within a pattern
![Sql Sql](/uploads/1/2/5/8/125805072/692889586.gif)
- 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 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 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 |