Wildcard representation operator in Access

WILDCARD, also known as a wildcard, can be used to determine the location of a specific item when you don't remember exactly how it was written.

Access supports two different wildcard sets that correspond to the standards of Structured Query Language (SQL):

1. ANSI-89: Use when using the Find and Replace dialog box to find and replace data in an Access database. You also use these characters when running select queries and updating with * .mdb and * .accdb files
2. ANSI-92: Use when running queries against Access projects, Access files associated with SQL Server databases.

ANSI-89 representative

*

Represents any character, with any number. Can be used * anywhere in a string.

wh* can find what , white and why , but not find awhile or watch .

?

Represent all single letter characters.

B?ll be able to find ball , bell and bill .

[]

Represent each single character in square brackets.

B[ae]ll can find ball and bell but can't find bill .

!

Represents all characters not enclosed in square brackets.

b[!ae]ll find the bill and bull but don't look for ball or bell .

-

Represents any character in the given range. This range is in ascending order (from A to Z, not from Z to A).

b[ac]d will find bad , bbd and bbd .

#

Represent all characters as digits.

1#3 will find 103 , 113 and 123 .

Character of ANSI-92 representative

%

Represents any character, with any number. Can be used as the first or last character in the character string.

wh% will find what , white and why but not looking for awhile or watch .

_

Represent all single letter characters.

B_ll will find ball , bell and bill .

[]

Represent each single character in square brackets.

B[ae]ll find the ball and bell but don't find the bill .

^

Represents all characters not enclosed in square brackets.

b[^ae]ll find the bill and bull but don't find ball or bell .

-

Represents any character in the given range. This range is in ascending order (from A to Z, not from Z to A).

b[ac]d will find bad , bbd and bbd .

For example

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

Add the following tables and close the Show Table dialog box .

Add the fields you want to appear in the query so they appear in the bottom design grid of the screen.

Execute the query by clicking the Run command on the Design tab , you will see results like this:

Now that you return to the query design screen, add the message phrase you want to appear when you execute the search query Project name: [Enter the project name:] .

See also: Parameter Query in Access 2016

Click Run on the Design tab and you will see the message appear:

Assume that you don't remember the project name correctly, but know that the project name contains the word "Cong nghe". Click OK.

This will not return results, because here Access will search for an exact result with the information entered in the Project Name field, and no project named Cong listens .

If you want to find a project that contains the word "Listen", you need to use wildcard and adjust the query criteria in addition to the LIKE operator.

Run the query and add the delegate operator to find the required data. Add * to replace the characters before and after the listener. Click OK.

This is a very useful way for those who know wildcard wildcards and want to search for data without 100% accuracy.

In addition, you can also use wildcards right from the query criteria operation, combined with LIKE and symbols " , & , for example:

 LIKE "*" & [Nhập tên dự án:] & "*" 

Rerun the query, this time you do not need the wildcard that comes with the keyword you are looking for.

Previous article: Relationship between tables in Access

Next lesson: Wildcard representation operator in Access