Access wildcard character reference

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

 Note   This topic provides reference information for the wildcard characters that you can use in Access.

For information about finding wildcard characters contained in the records in an Access database, see Find wildcard characters in an Access database. For information about ways to run find and find-and-replace operations in an Access database, see Find and replace part or all of a record in an Access table, query, or form.

Contents

About supported ANSI standards

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 find or find-and-replace operations against Access databases. You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Because SQL Server uses the ANSI-92 standard, Access projects also use the ANSI-92 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 wildcard character set that you use with each.

 Note   Access 2000 supports only the ANSI-89 standard.

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

Access database set to support the ANSI-92 standard

ShowHow do I know which standard a database supports?

Follow these steps to find and optionally change the ANSI setting for a given database. Keep in mind that Access 2000 supports only the ANSI-89 standard.

  1. On the Tools menu, click Options, and then click the Tables/Queries tab.
  2. Under SQL Server Compatible Syntax (ANSI 92), select the This Database check box if you want to use the ANSI-92 standard, or clear the check box to use the ANSI-89 standard.
  3. If you change the setting, click OK, and then click OK again to close the alert message. Access closes and reopens the database.
ANSI-92

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.

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, 123

ANSI-92 wildcard characters

Use this set of wildcard characters when you run queries against Access projects (.adp files), and when using queries 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 that rule when searching 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 using the Find and Replace dialog box, simply enter the character in the Find What box with no surrounding brackets. You follow the same approach when finding the characters with a query. For example, this syntax returns all records that contain an exclamation point: Like "*!*".

For information about using the Find and Replace dialog box and queries, see Find wildcard characters in an Access database.

If you're 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: [!-].

Data types you can search using wildcards

When you design a table, you apply a data type to each field in that table. For example, you apply the Date/Time data type to fields that contain date information. This table lists 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. That is why Access usually enables the Search Fields as Formatted check box when you search dates. Also remember that a computer's date and time setting can affect what you see and how you search. For example, Access may render parts of some dates as Roman numerals, such as 07-IX-1997 instead of 07-Sept-1997. As a rule, you search using what you see, not what you think Access stores in the table or "under the hood," as it were. In other words, you can search using a string such as *-IX-1997 to find all records for September of that year.

ShowShow me.

These steps explain how to change your computer's regional date and time settings in Microsoft Windows, and then search the Northwind Traders sample database by using the Find and Replace dialog box and wildcard characters.

To change your regional date and time setting
  1. Click Start, click Control Panel, and then double-click Regional and Language Options.
  2. Click the Regional Options tab, and under Standards and Formats select another language from the list. Select a language that uses short and long dates in a format other that what you normally use. For example, if you live in an English speaking country/region, try a language such as Czech or Finnish.
  3. Click OK.
To search by using the Find and Replace dialog box
  1. Open the Northwind Traders sample database and then open the Orders table.

ShowHow?

  1. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
  2. If the Security Warning dialog box appears, click Open.
  3. Click OK to close the welcome screen.
  4. On the Main Switchboard, click Display Database Window.
  5. In the Database window, click Tables under Objects.
  6. Double-click the Orders table, or select it and then click Open Button image.

The table opens in Datasheet view — a grid similar to an Excel workbook.

  1. Click one of the date fields, and then on the toolbar, click Find Button image. You can also click Find on the Edit menu, or press CTRL+F.

The Find and Replace dialog box appears.

  1. In the Find What box, enter a wildcard and part of the date as Access renders it. For example, if Access displays part of the date in Roman numerals, use that type of numeral in your search string. For example you could type *IX-1997.
  2. From the Match list, select Any Part of Field, from the Search list, select All, and select the Search Fields as Formatted check box if it isn't already.

 Note   You may need to experiment with clearing or selecting the Search Fields as Formatted check box. If selecting the check box fails to return any results, clear the check box and try the operation again.

  1. Click Find Next. Access cycles through the records.

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ä-1997, you can search with a string such as *-hein*-1997.

  • When you try to search a Yes/No field by using the Find and Replace dialog box, Access disables 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 can not search OLE Object fields.

Search Date/Time fields with queries that contain wildcards

You can use wildcard characters in queries that search Date/Time fields, but keep a key point in mind: Unlike the Find and Replace dialog box, queries do not take formatting into account. A good way to see what this means is to create a simple select query on a Date/Time field and use wildcard characters to search that field — first with, and then without formatting applied. The following steps explain how.

To create a simple query
  1. Open the Northwind Traders sample database and display the Database window.
  2. Under Objects, click Tables and then click the Orders table to select it.
  3. On the Insert menu, click Query from the list. The New Query dialog box appears.
  4. Select Design View and then click OK. The query design grid appears. Because you selected the Orders table in step 2, that table appears at the top of the design grid.
  5. Drag the OrderDate field to the Field row in the first column of the query design grid.
  6. In the Criteria row, type Like "*10*" and then click Run Button image to run the query.

The query returns records that contain 10, such as 10-Jul-1996, and it also returns records that don't seem to contain 10, such as 03-Oct-1997. Why? Because in the Gregorian calendar, October is the 10th month, and Access disregards the formatting when you use wildcard characters in a query. Go to the next steps and see how removing the format can help you remove the confusion that query results can sometimes cause.

To run the query with no formatting
  1. Open the Orders table in Design view.
  2. Select the OrderDate field.
  3. On the General tab (at the bottom of the table design grid), clear the format from the Format field by highlighting dd-mmm-yyyy and pressing DEL.
  4. Save your changes and close the table design grid.
  5. Rerun the query you created in the previous steps.

With the format removed, Access displays the dates numerically ("mm/dd/yyyy" format), and each of the dates returned by the query contains "10". So if a query seems to return unwanted results, try removing any formatting and rerunning the query.

Also, you can use this approach to filter data. For example, the criterion Like "10*" returns records only for October, and Like "*10/1996" returns orders placed only on the 10th day of any month in 1996.

To restore the OrderDate field to its original state, repeat steps 1 and 2 above and type dd-mmm-yyyy in the Format field.

 
 
Applies to:
Access 2003