Find wildcard characters in an Access database

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

 Note   This article is part of a larger topic titled Find part or all of a record in an Access table, query, or form. That topic provides a more complete discussion of the tools and processes described here. This topic explains only how to find and optionally replace wildcard characters. The larger topic explains how to find and replace other types of data.

You can find and replace wildcard characters such as asterisks (*) or pound signs (#) in an Access database. But to do so, you must enclose the characters that you want to find within square brackets, like so: [#].

Keep these facts in mind:

  • You can use the Find and Replace dialog box, select queries, and update queries to find and replace wildcard characters. The how-to sections in this topic explain how to use each tool, and the advantages and best uses for each.
  • Regardless of the tool that you use, keep in mind the rule mentioned earlier: Enclose the wildcard characters that you want to find within brackets: []. You follow that rule when you search for all wildcard characters except exclamation points (!) and closing brackets (]).

That rule applies when you use the Find and Replace dialog box and when you run a select query. However, the rule does not apply when you run an update query. The how-to steps later in this topic show you how to use wildcard characters in each type of query.

  • If you want to find pairs of brackets ([]) you must enclose them within brackets, like so: [[]]. If you search on a single pair of brackets, Access interprets that combination of characters as a zero length string — a string that contains no characters. You use zero-length strings to indicate fields that contain no values. For more information about zero-length strings, see the topic Find part or all of a record in an Access table, query, or form.

What do you want to do?

  • Use the Find and Replace dialog box with wildcard characters
    Explains how to use the dialog box to find and optionally replace wildcard characters in forms, tables, and query results. It also briefly explains how to use wildcard characters in replacement strings, and it provides a table of examples that demonstrate some of the ways that you can use wildcards and how the various options in the Find and Replace dialog box affect the results of your find operations.
  • Use queries to find and replace wildcard characters
    Explains how to create select and update queries, and how to use wildcard characters in both types of queries. The section also explains the best ways to use each type of query.
  • Wildcard character reference
    A list of the ANSI-89 wildcard characters that Access supports and brief descriptions of how to use each character. The section also provides a link to the larger Access wildcard reference, plus links to topics about the ANSI SQL modes that govern the sets of wildcard characters that you can use.

Top of Page Top of Page

Use the Find and Replace dialog box with wildcard characters

The steps in the following sections explain how to find and replace wildcard characters by using the Find and Replace dialog box. These steps use the Northwind Traders sample database. The data in Northwind does not contain wildcard characters, so you'll add some to the records in a table, and then run find operations and find-and-replace operations against those records. You can adapt the steps for use with your own data, and you can skip steps that don't apply to your situation.

Add wildcard characters to records

Skip the steps in this section if your data already contains wildcard characters.

  1. ShowOpen the Northwind Traders sample database and then open the Orders form.

  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 Orders.

The Orders form opens in Form (normal) view.

  1. In the Ship To list, select a record and type an asterisk or other wildcard character. For a list of valid wildcard characters, see the section Wildcard character reference. Scroll through the records to select another customer and repeat the process. Keep the form open and go to the next section.

 Note   These steps use the records in the Ship To list because that control takes its data from the underlying Orders table. Other controls in the form, such as the Bill To list and the product subform (the list of products and prices), are bound to Lookup fields — fields that use queries to take data from other tables. You cannot use the Find and Replace dialog box to change the data in a Lookup field. Instead, you have to change the data in the table that serves as the source for the Lookup field. If you try to run a find-and-replace operation on a Lookup field, Access displays an error message and stops the operation. For more information on this issue, see the topic Find part or all of a record in an Access table, query, or form.

Find records that contain wildcard characters

  1. Open the form, table, or query result that contains the characters that you want to find. If you want to use the Northwind Traders sample database, follow the steps in the previous section to add some wildcard characters to the Ship To field in the Orders table.
  2. 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. If it isn't already selected, click the Find tab.
  2. In the Find What box, type an opening bracket ([), the wildcard character that you want to find, and a closing bracket (]). For example, if you want to find all instances of the asterisk, type [*].
  3. Make sure that the Look In list contains Ship To, or the name of the column in your database.
  4. In the Match list, click Any Part of Field.
  5. Make sure that the Search Fields As Formatted check box is selected, and then click Find Next. The find operation returns the records that contain the wildcard character. If you continue to click Find Next, the operation cycles through the remaining records.

Use wildcards in find-and-replace operations

A find-and-replace operation uses two strings of characters — a search string (the characters that you want to find), and a replacement string. As a rule, you use wildcards only in search strings. If you use wildcard characters in replacement strings, Access treats them as literal characters and writes them to your database.

For example, say you use the search string old* to search the orders in the Northwind database. Depending on the option that you select from the Match list, Access finds records that start with or contain "old", such as Old World Delicatessen and Consolidated Holdings. But if you use a wildcard in the replacement string, such as new*, Access overwrites the record with your replacement string. Put another way, "Old World Delicatessen" would become "new*", and nothing more. For that reason, you need to be choose your replacement strings with care.

These steps use the records that were altered in the previous sections. You can adapt these steps to fit your data.

  1. Open the Orders form, or open the Orders table in Datasheet view.
  2. 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. Click the Replace tab.
  2. In the Find What box, type the wildcard character that you typed in the previous section, and enclose the character with brackets ([]). For example, if you typed an asterisk, type [*].
  3. In the Replace With box, type your replacement string. For example, if you added wildcard characters to records containing Old World Delicatessen and you want to eliminate the wildcards, type Old World Delicatessen in the box.
  4. Make sure that the Look In list contains Ship To or the name of the column in your database.
  5. In the Match list, click Any Part of Field.
  6. If the Search Fields As Formatted check box is selected, leave the check box selected and then click Find Next. If the operation finds the correct record and you want to replace that record, click Replace. If you're sure that the operation will replace only the records that you want, click Replace All. Depending on the option that you choose, Access cycles through the remaining records.

Examples of wildcard characters in use

When you know the type of pattern that each wildcard character can match, you can use combinations of wildcards, or wildcards and literal characters, to return a variety of results. Keep in mind that the setting that you choose in the Match list affects your search results. If you use an incorrect setting, your search operation may return unwanted data, or it may not return any results at all. The following table shows some ways to use wildcards and literals in the Find and Replace dialog box, and it explains how the options in the Match list can affect your results.

 Note   You can also use the examples listed here in select and update queries.

Search string Match list setting Results
[*] Any Part of Field Returns all records that contain an asterisk (*). This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
Whole Field Returns records that consist only of an asterisk.
Start of Field Returns records that start with an asterisk.
*[*]* Any Part of Field Returns all records that contain an asterisk (*) and any surrounding text. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
Whole Field Same result.
Start of Field Same result.
[!*] Any Part of Field

Returns all records that do not contain an asterisk. Keep in mind that this search pattern can return every letter of every word in a record when you use this setting in the Match list. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).

 Note   The search string *[!*]* will return records that contain asterisks because it finds all the text that surrounds the asterisk.

Whole Field Returns no results at all.
Start of Field Returns the first letter of any record that does not contain an asterisk.
ma*[ch] Any Part of Field Returns all records that contain "ma" and either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
Whole Field Returns all records that start with "ma" and end with either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
Start of Field Returns all records that start with "ma" and contain "c" or "h".
ma*[!ch] Any Part of Field

Highlights the letters "m" and "a" and all text that follows those letters until it encounters a "c" or an "h". The following figures illustrate this.

Partial pattern match

Another partial pattern match

In other words, even though you're trying to exclude records that contain "c" and "h", you may see those records because Any Part of Field matches the text that precedes the brackets.

Whole Field Returns all records that do not contain a "c" or an "h" if those records end in "c" or "h". For example, the find operation does not return "manic" because the word ends with a "c", but it does return "maniacal" because characters follow the "c".
Start of Field Returns those records that start with "ma". Again, Access matches any text that precedes the characters enclosed in brackets, so you may see unwanted results.

Top of Page Top of Page

Use queries to find and replace wildcard characters

A query can find and replace wildcard characters faster and with less effort than the Find and Replace dialog box. Queries are also more powerful. For example, you can use multiple criteria in a query and find all instances of each type of wildcard character, which you can't do with the Find and Replace dialog box.

You can use two types of queries to find and replace wildcard characters and other data — select queries and update queries. A select query displays its results in the form of a datasheet — a row and column grid similar to a Microsoft Excel worksheet — and you can remove or change any wildcard characters manually in that data sheet. In contrast, an update query finds records that match the criteria that you specify, and then it replaces those records with a second criteria called an update criteria. If it helps, you can think of an update query as a more powerful version of the Find and Replace dialog box. You can find all instances of each type of wildcard character and then replace the instances with other data, all in one pass.

The following sections demonstrate how to use each type of query to find and optionally replace wildcard characters. If you aren't familiar with either type of query, see the topic Create a select or crosstab query (MDB) or Update existing data with an update query.

The steps use the Northwind sample database, but you can adapt the steps for use with your data.

Use a select query to find and replace wildcards

  1. If you haven't already done so, open the Northwind Traders sample database, and then open the Database window.

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.
  1. On the Objects bar, click Queries, and then double-click Create query in Design view. The design grid and the Show Table dialog box appear.
  2. In the Show Table dialog box, double-click Orders, and then click Close.
  3. In the Orders table in the design grid, double-click ShipName.
  4. In the Criteria field, type Like "*[*]*". The query design grid should look like the following.

Query that searches records containing wildcards

  1. Click Run Button image. The resulting datasheet contains any records that contain the wildcard specified in the Criteria field.
  2. You can manually remove any unwanted wildcard characters.

In this case, the query uses additional wildcards (the asterisks on either side of the brackets) to return all the text in any record that contains an asterisk. If you think your data contains other wildcard characters, you can use "or" clauses to query for additional characters. The following steps explain how:

  1. In the query design grid, click the or field directly below the Criteria field.
  2. Type the statement that you used in the Criteria field (you can copy and paste it), but replace the asterisk with another wildcard character — for example, Like "*[?]*", or Like "*[#]*". You can add as many statements as needed to individual or fields, or you can type the statements into a single field, such as Like "*[*]*" Or Like "*[#]*" Or Like "*[?]*". Remember to omit the brackets when you query for exclamation points and closing brackets — for example, Like "*!*" Or Like "*]*"
  3. Run the query again and manually remove any unwanted wildcard characters.

 Notes 

  • The query uses the Like operator because that operator searches for patterns in text fields.
  • You can use combinations of literal text and wildcard characters if you need to narrow your search. For example, you can use Like "Alfreds Futterkiste!" Or Like "Alfreds Futterkiste[*]" to search for records that contain only that vendor or customer.
  • To find pairs of empty brackets, you enclose them with brackets, such as [[]]. If you don't enclose the empty brackets inside another pair of brackets, Access interprets the brackets as zero-length strings, and your search operation fails. For more information about zero-length strings, see the topic Find part or all of a record in an Access table, query, or form.

Use an update query to find and replace wildcards

When you create and run an update query, it's a good idea first to create a select query to ensure that you're working with the correct set of records. To demonstrate this idea, in this section you create a select query and add wildcard characters to the records for a single customer. You then create an update query to remove those wildcard characters. As part of that process, you leave the select query open and see how the update query changes the records.

To prepare the sample data
  1. If you haven't already done so, start the Northwind Traders sample database and then open the Database window.

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.
  1. Under Objects, click Queries, and then double-click Create query in Design view.

The design grid and the Show Table dialog box appear.

  1. In the Show Table dialog box, double-click Orders and then click Close.
  2. In the Orders table in the design grid, double-click ShipName.
  3. Click the Sort field and select Ascending from the list.
  4. Click Run Button image to run the query. The result set (the datasheet) displays six records that contain Alfreds Futterkiste.
  5. Manually add a pound sign (#) to each of those six records. Leave the query open, and go on to the next set of steps.

Top of Page Top of Page

To change the sample data with an update query
  1. Click the Database window, and then click New.

The New Query dialog box appears.

  1. In the New Query dialog box, click Design View and then click OK.

The design grid and the Show Table dialog box appear.

  1. In the Show Table dialog box, double-click Orders and then click Close.
  2. In the Orders table in the design grid, double-click ShipName.
  3. On the main toolbar, click Query Type Button image and then click Update Query.

The fields in the design grid change.

  1. In the Criteria field, type #Alfreds Futterkiste, and in the Update To field, type Alfreds Futterkiste without the wildcard character.

The value in the Criteria field becomes your search string, and the value in the Update To field becomes your replacement string.

  1. Click Run Button image and then click Yes to close the warning message.

If you left the datasheet from the select query open, you can see how the records change.

Top of Page Top of Page

Wildcard character reference

The following table lists the ANSI-89 wildcard characters that you use to search Access databases (.mdb files).

Character Description Example
* (asterisk) Matches any number of characters, including spaces. You can use the asterisk anywhere in a string. wh* finds "what", "white", and "why", but not "watch" or "awhile".
s*d finds "sad" and "started".
? Matches any single alphabetic character. b?ll finds "ball", "bell", and "bill".
[ ] Matches any single character within the brackets, including other wildcard characters. Use the brackets when you need to find wildcard characters. 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".

 Note   You also use the ANSI-89 wildcards when you use the Find and Replace dialog box to search an Access project (an Access file connected to a Microsoft SQL Server database). However, you use a different set of wildcards — ANSI-92 — when you run queries in an Access project. Queries in an Access project support ANSI-92 because SQL Server uses that standard. For more information about the ANSI-92 wildcards, see the topic Access wildcard character reference. In addition, you can set and change the ANSI standard that an Access database supports. For more information about doing so, see the topics About ANSI SQL query mode (MDB) and Set ANSI SQL query mode (MDB).

Top of Page Top of Page

 
 
Applies to:
Access 2003