MS Access wildcard
May 16, 2021 MS Access
Table of contents
Wildcards are special characters that can represent unknown characters in text values and can be used to find multiple items with similar but not identical data. Wildcards can also help get the database on a specified pattern match.
Access supports two sets of wildcards because it supports two structured query language standards.
- ANSI-89
- ANSI-92
Typically, you can use the ANSI-89 wildcard when you run queries and find replacement operations on the Access database, such as the s .mdb and s .accdb files.
Use the ANSI-92 wildcard when running queries on the Access project - access files connected to the Microsoft SQL Server database. Access projects use the ANSI-92 standard because SQL Server uses that standard.
ANSI-89 wildcard
The following table lists the characters supported by ANSI-89 -
| Character | Describe | Example |
|---|---|---|
| * | Matches any number of characters. You can use an asterisk anywhere in the string. | WH - Find what, white, why, not for a while or watch. |
| ？ | Matches any single letter character. | B'll be able to find the ball, Bell and the Bills. |
| [] | Matches any single character in parentheses. | B (Auto Exposure) ll can find balls and bells, but not bills. |
| ！ | Matches any character that is not in parentheses. | B〔！ AE ll can find bill and bull, not balls or clocks. |
| - | Matches any one of the character ranges. You must specify the range in ascending order (A to Z, not Z to A). | B (AC) d can be found well, BBD, and BCD. |
| ＃ | Matches any single numeric character. | 1' 3S identified 103, 113 and 123. |
ANSI-92 wildcard
The following table lists the characters supported by ANSI-92 -
| Character | Describe | Cases |
|---|---|---|
| ％ | Matches any number of characters. It can be used as the first or last character in a string. | WH% Find What, White, Why, not For a while or watch. |
| _ | Matches any single letter character. | B_ll found the ball, Bell and the Bills. |
| [] | Matches any single character in parentheses. | B (Auto Exposure) ll can find balls and bells, but not bills. |
| ^ | Matches any character that is not in parentheses. | B.AE. ll can find bill and bull, not balls or clocks. |
| - | Matches any one of the character ranges. You must specify the range in ascending order (A to Z, not Z to A). | B (AC) d can be found well, BBD, and BCD. |
Example
Let's look at a simple example of using these wildcards by opening a query design.
Add a highlighted table, and then close the Show Table dialog box.
Add the fields in the query grid to the query results that you want to see.
Run the query.
Let's go again to query the design and add a hint of the project name.
Now run your query and let's assume that you don't know the exact project name, but you know that the project name contains the word "potion". Click Yes.
The above steps do not produce any results. T his is because Access looks for exact matches in the Project Name field. It is looking for the project that has the word potion in its name.
If you want users to enter wildcards to replace unknown characters, you need to adjust the conditions and include the operator.
When you run a query, users can replace any number of characters with wildcards.
Let's assume you know that the word potion appears somewhere in the title, but you're not entirely sure where it is.
You can add a s to replace any number of characters before the word potion, and then add another character. Click OK.
It is always beneficial for users to know the wildcards they can access. H owever, some users may not know what wildcards they can and cannot enter. In this case, you can enter the wildcard yourself.
In this case, between the Like operator and our parameter prompts, we can add these wildcards, and now there is a very specific way that we have to write this. b20> b30> e will now add this to the parameters. """ e'll now repeat this step and add another sign, because we'll add the wildcard so that the user can enter any conditions for the item name, and then use the """"""""
Let's run our query again. Enter the word potion in the prompt without any wildcards.
The query will now track wildcards on 1st side entered here. We need to simply enter the potion and press Enter.
Now that we've got the results we're looking for, the results will be the same, no matter what the user enters.
For example, we want to find the "the" in the title of each project. Then, you just type the word and Enter or click OK.
With this query, it is easier to search for items with the word "the" in their names. The second-level results can also include the project name and the word "mother", where "the" is part of the word.