Match all characters anywhere in your data
- 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.
I am using Microsoft Access 2010 for running the SQL query. Some rows has empty / null phone values. VB Form and MS Access SQL Wildcard Search. The query will. Access supports two sets of wildcard characters because it supports two standards for Structured Query Language. ANSI-89 ANSI-92 As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases such as.mdb and.accdb files. Feb 03, 2014 Good Day, I am trying to make a Search CombBox on a Form capable of searching with WildCard rather than exact match. It is pulling from one column of a query.
Match a character within a pattern
- 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. Software de camara web microsoft lifecam vx-1000. | 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 |