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
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 |
Find which ANSI standard a database supports
Follow these steps to find and optionally change the ANSI setting for a given database.
-
Click File > Options. Note, if you're using Access 2007, click the Microsoft Office Button, and then click Access Options.
The Access Options dialog box appears.
-
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.
-
-
Click OK.
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. |
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.
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.
No comments:
Post a Comment