MS Access - Wildcards
Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting databased on a specified pattern match.
Access supports two sets of wildcard characters because it supports two standards for Structured Query Language.
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.
You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Access projects use the ANSI-92 standard because SQL Server uses that standard.
ANSI-89 Wildcard Characters
The following table lists out characters supported by ANSI-89 −
| Character |
Description |
Example |
| * |
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 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#3S finds 103, 113, and 123. |
ANSI-92 wildcard characters
The following table lists out characters supported by ANSI-92 −
| Character |
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, but not awhile or watch. |
| _ |
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. |
Example
Let us now look at a simple example of using these wildcard characters by opening the query design.
Add the highlighted tables and close the Show Table dialog box.
Add the fields in the query grid which you want to see as a query result.
Run your query.
Let us again go to the query design and add prompt for project name.
Now run your query and let us assume that you don’t know the exact project name, but you know that the project name contains the words “potion”. Click Ok.
The above step does not generate any result. That is because Access is looking for an exact match in the project name field. It is looking for that project which has the word potion in its name.
If you want it so that the users can enter wildcards to replace unknown characters, then you need to adjust the criteria and include the operator like.
When you run the query, users can use wildcards to replace any number of characters.
Let us assume that you know that the word potion appears somewhere in the title but you are not exactly sure where.
You can add an * to replace any number of characters before the word potion and then add another * after the. Click Ok.
It is always good for the users to know the wildcards they can enter. But there are users who may not know the wildcards they can and cannot enter. In that case, you can enter the wildcards yourself.
In this situation, in-between the Like operator and our parameter prompt, we can add those wildcards and now there's a very specific way we have to write this. After the word like in quotation marks, enter the wildcard that we are using. In this case, we have used “*” to replace any number of characters. We will now add this to the parameter. To do so, we need ampersand (&) symbol and a space. We will now repeat this step and add another ampersand (&) because we're joining that wildcard to whatever criteria is entered in for that project name by the user and then “*” in quotes.
Let us now run our query again. Enter the word potion in the prompt without any wildcards.
The query will now track those wildcards on either side of whatever is entered here. We need to simply type potion and press enter.
We now get the results we were looking for and the results will be the same regardless of what the users enter.
Let us say we want to find every project with the word “the” in the title. Then, you need to just type the word and Enter or click Ok.
With this query, it becomes easier to search for projects with the word “the” in their names. The 2nd level of results may also include the project names with the word “mother” where “the” is part of the word.
