Access wildcard character reference

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.

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.

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.

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.

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