Access wildcard character reference

This topic provides reference information and limited how-to information for using the wildcard characters provided by Access.

For information about finding wildcard characters in an Access database, see the articles Use the Find and Replace dialog box to change data, Create a simple select query, and Update the data in a database.

In this topic

• About supported character sets

• Find which ANSI standard a database supports

• ANSI-89 wildcard characters

• ANSI-92 wildcard characters

• Data types you can search for by using wildcards

About supported character sets

Access supports two sets of wildcard characters because it supports two standards for Structured Query Language — ANSI-89 and ANSI-92. As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases — .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.

However, Access also provides an exception to that rule. The following table lists the methods or tools that you can use to find and replace data, and shows the default ANSI standard that you use with each tool.

Search method or tool	Type of file searched	Wildcard character set used
Find and Replace dialog box	Access database (.mdb and .accdb files)	ANSI-89
Find and Replace dialog box	Access project (.adp and .accdp files)	ANSI-92
Select or update query	Access database (.mdb and .accdb files)	ANSI-89
Select or update query	Access project (.adp and .accdp files)	ANSI-92
Find and Replace dialog box, select or update query	Access database set to support the ANSI-92 standard	ANSI-92

Top of Page

Find which ANSI standard a database supports

Follow these steps to find and optionally change the ANSI setting for a given database.

1. Click File > Options. 

   The Access Options dialog box appears.

2. Click Object Designers, and in the Query design section, under SQL Server Compatible Syntax (ANSI 92), do one of the following:

   • Select This database to change the open database to the ANSI-92 standard.

     -or-

     Clear the check box to set the open database to the ANSI-89 standard.

   • Select Default for new databases to set all new databases created with the open instance of Access to the ANSI-92 standard.

     -or-

     Clear the check box to set all new databases to the ANSI-89 standard.

3. Click OK.

Top of Page

ANSI-89 wildcard characters

Use this set of wildcard characters when you use the Find and Replace dialog box to find and optionally replace data in an Access database or an Access project. You also use these characters when you run select and update queries against an Access database, but you do not use them in queries run against an Access project. For more information about using select and update queries, see the articles Create a simple select query and Update the data in a database.

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#3 finds 103, 113, and 123.

Top of Page

ANSI-92 wildcard characters

Use this set of wildcard characters when you run select and update queries against Access projects (.adp files), and when using either type of query or the Find and Replace dialog box to search databases set to use the ANSI-92 standard.

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.

NOTES:

• To find wildcard characters that reside in your data, enclose the character that you want to find in brackets, like so: [#]. Follow this rule when you search for asterisks (*), question marks (?), pound signs (#), opening brackets ([), and hyphens (-). Do not use brackets when searching for exclamation points (!) or closing brackets (]). To find those characters by using the Find and Replace dialog box, type the character in the Find What box with no surrounding brackets. You follow the same approach when finding the characters by using a query. For example, the following syntax returns all records that contain an exclamation point, regardless of where the character resides in your data: Like "*!*".

  For information about using the Find and Replace dialog box, see the article Use the Find and Replace dialog box to change data. For information about using select and update queries, see the articles Create a simple select query and Update the data in a database.

  If you are searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets, like so: [-#*] or [#*-]. However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point: [!-].

• To search for a pair of opening and closing brackets ([]), you must enclose both characters in brackets, like so: [[]]. You must do this because Access interprets a single pair of brackets as a zero-length string.

Top of Page

Data types you can search for by using wildcards

When you design a table, you set a data type to each field in that table. For example, you set the Date/Time data type for fields that contain date information. This table lists the data types that you can search by using wildcards. Remember that in some cases, you can use wildcards in the Find and Replace dialog box but not in queries, and vice-versa.

Data Type	Use In ...
Text	Find and Replace dialog box, queries
Memo	Find and Replace dialog box, queries
Number	Find and Replace dialog box, queries
Date/Time	Find and Replace dialog box, queries Note: Regional settings can affect the way you use wildcards. See the notes at the end of this section for more information.
Currency	Find and Replace dialog box, queries
AutoNumber	Find and Replace dialog box, queries
OLE Object	None.
Yes/No	Queries, but you don't need them. For more information, see the notes at the end of this section.
Hyperlink	Find and Replace dialog box, queries
Lookup Wizard	Depends on the data type of the source field.

NOTES:

• You can use wildcards in the Find and Replace dialog box to search Date/Time fields if the format applied to those fields displays part or all of the date as text. For example, you can search by using a string such as *ar*-10-2007, and your results would include any month that contains the letters "ar" — January, February, and so on. Remember that because you must search by using the format applied to data, you must select an option in the dialog box — the Search fields as formatted check box. For more information about this check box, see the article Use the Find and Replace dialog box to change data.

• The date and time specified in your Windows Regional Settings can affect what you see and how you search. For example, some users may see dates as Roman numerals, such as 07-IX-1997 instead of 07-Sept-1997. As a rule, you search on what you see, not what you think Access stores in the table. In other words, you can search by using a string such as *-IX-2007, to find all records for September of that year.

  In addition, if text in a date field contains diacritical marks, such as á or ä, you must include those marks in your search string or the search will fail. You can work around the presence of diacritical marks by using wildcards. For example, if you see a date such as 3-heinä-2007, you can search by using a string such as *-hein*-2007.

• When you search a Yes/No field by using the Find and Replace dialog box, Access ingores the field, and the dialog box does not return any records. When you search a Yes/No field by using a query, you can use wildcards, but keep in mind that Yes/No fields only return two values (0 for false and -1 for true), so a wildcard doesn't add any value to the search. For example, using a criterion such as =-1 returns the same results as "Like *1".

• You cannot search OLE Object fields.

Top of Page