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.

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.

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 psts out characters supported by ANSI-89 −

ANSI-92 wildcard characters

The following table psts out characters supported by ANSI-92 −

Example

Let us now look at a simple example of using these wildcard characters by opening the query design.

Add the highpghted 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”. Cpck 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 pke.

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. Cpck 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 pke 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 cpck Ok.

With this query, it becomes easier to search for projects with the word “the” in their names. The 2^nd level of results may also include the project names with the word “mother” where “the” is part of the word.