Find and replace part or all of a record in an Access table, query, or form

This topic explains how to use the Find and Replace dialog box, select queries, and update queries to locate and optionally replace records in a Microsoft Office Access 2003 database. This topic also explains how to find and optionally remove or replace wildcard characters that reside in your data.

In addition, this topic briefly explains some of the techniques you can use for learning the structure of an Access database. Searching a database can be a complex process because the data can reside in any number of tables. If you know the structure of a database — the types of data each table contains, which tables have one-to-one and one-to-many relationships, and so on — data can be much easier to find.

Finally, this topic provides a reference that explains how to use the wildcard characters that Access provides, and a troubleshooting section that explains how to solve common problems with find and find-and-replace operations.

 Note   Finding and replacing data is not the same thing as sorting or filtering data. For information about sorting data, see the topic Sort records. For information about filtering data, see the topic Filter: Limit the number of records in a view or report.

Contents

  • About finding and replacing records in an Access database
    Explains the concepts behind find and find-and-replace operations, plus the differences between finding, sorting, and filtering data. This section also explains some ways to familiarize yourself with the structure of an Access database, and what to do when you encounter Lookup fields.
  • Find and replace data
    Explains how to use the Find and Replace dialog box, select queries, and update queries to locate and optionally replace records in forms, database tables, and query results. Steps in the section also explain how to find empty or null values, zero-length strings, and quotation marks in records.
  • Use wildcard characters in find and find-and-replace operations
    Explains how to use wildcard characters with the Find and Replace dialog box, and in select and update queries. The section also explains how to find wildcard characters contained in your data.
  • Troubleshoot find and find-and-replace operations
    Explains how to fix common problems encountered during find operations.

About finding and replacing records in an Access database

Access provides several tools for finding and optionally replacing wildcard characters that reside in database records:

  • The Find and Replace dialog box. Use the dialog box when you need to find or replace small amounts of data, and when you aren't comfortable using a query to find or replace data.
  • Select queries. Select queries can find whole sets of records, they can search multiple tables (something the dialog box can not do), and you can make changes directly to the data in the query result.
  • Update queries. Update queries work best when you need to find and optionally remove or replace data in a large number of records. Keep in mind that update queries work best if the wildcard characters reside in the same places in a given set of fields. Otherwise, you must change your criteria to match each occurrence of all the wildcards, a process that may take more time than removing unwanted characters manually.

When you run a find operation using the Find and Replace dialog box or a select query, you enter a search string — a string of text or a string of text and wildcard characters. The dialog box treats that string as a pattern and returns all records that match the pattern. For example, say you have a datasheet (either a table or a query result) that includes information on customers, suppliers, products, and order dates. If you search on "mar", the find operation will return all instances of "market", "March", and any other records that match the pattern you specified.

When you run a find-and-replace operation using the Find and Replace dialog box or an update query, you enter a search string and a replacement string. When you run the operation, Access finds all records that match your search string, and then overwrites those matches with your replacement string. For example, say that one of your customers changes its name from Old World Delicatessen to Contoso Foods. You can search for all instances of the original name and replace them with the new name.

As you proceed, keep these facts in mind:

  • You can use the Find and Replace dialog box to search for data based on a format applied to that data. For example, you can format empty or null fields to display a word such as "Unknown." You can then search for those blank fields using "Unknown" as your search string.
  • You can use the Find and Replace dialog box to search the datasheets (either tables or query results) and forms in a database. To run a find operation with the dialog box, you must open tables and query results in Datasheet view, and you must open forms in Form (normal) view, just as you would when entering data.

Those facts may seem obvious, but they can be easy to overlook because other Office programs work differently. For example, when you run a find operation in a Microsoft Word document or Excel workbook, you search the whole file, and over time you get used to searching entire files. Access is different. Your tables, forms, queries, and other objects may reside in a single Access database (an .mdb file), but a find operation searches the individual components, not the whole database.

  • Remember that your data can reside in several tables. For example, say you want to find a specific order and the name and address of the customer who placed that order. In a well-designed relational database, that information almost always resides in multiple tables — one for order data and another for customer data. Because find operations search only one datasheet or form at a time, knowing where given pieces of data reside in a database can save a lot of time. For information about ways to explore an Access database, see the section Familiarize yourself with a database, later in this topic.
  • One of the best ways to find data is to create a select query to gather the desired data, and then search the query result. Select queries can return data from multiple tables, something the Find and Replace dialog box cannot do. This topic explains the basics of creating select queries, but queries can become very complex, and providing advanced information on them is beyond the scope of this topic. For more information about select queries, see the topic Create a select or crosstab query (MDB).

You can also use wildcards in find and find-and-replace operations, but you must use them carefully. Keep these rules in mind:

  • You can use the Find and Replace dialog box, select queries, and update queries to find wildcard characters that reside in your data. If you use the dialog box or a select query, you surround the character that you want to find in brackets, like so: [*]. You follow that rule when searching for all wildcard characters except exclamation points (!) and closing brackets (]).

Like many rules, this one has an exception: You do not use brackets when you use an update query to find and replace wildcards.

  • With either the Find and Replace dialog box or an update query, if you use a wildcard character in a replacement string, Access treats that character as a literal and writes it to your database. For example, if you search using old * and replace all records that match that pattern with new *, Access writes "new *" to your database.

For more information about finding wildcards, see Use wildcard characters in find and find-and-replace operations later in this topic.

The differences between finding, sorting, and filtering

Finding is the process of locating one or more records in database tables, queries, and forms. For example, you can find all the customers who run restaurants. Sorting is process of organizing a set of records in a particular order. For example, you can sort a set of records in alphabetical order, by date, by quantity (which restaurant orders the most bread or has the highest sales), and so on. Filtering is the process of displaying records that meet a given condition. For example, after you find all the customers who own restaurants, you can filter the list to include only customers in London or Madrid.

If it helps, you can think about it this way: You first find the data, optionally arrange the data in a given order, and then optionally use a filter to isolate a subset of the data.

Familiarize yourself with a database

Becoming familiar with the tables, forms, and other objects in a database can make it easier to find records and replace data. For example, if you try to run a find-and-replace operation on a type of field called a Lookup field (a field in a table that draws data from another table), Access displays an error message. How do you know if you're dealing with a Lookup field? By knowing the structure of the database. The section Explore tables in Design View explains how to find Lookup fields in tables, and how those fields affect find operations.

Access provides several tools to help you learn the structure of a database. The steps in the following sections provide basic information about how to use the Database Documenter, explore a table in Design view, use the Relationships window, and use the Object Dependencies task pane. The steps use the Northwind Traders sample database, but you can adapt the steps for use with your own data.

ShowExplore databases using the Database Documenter

You use the Database Documenter when you need detailed information about a database, and you want to print that information. The Database Documenter generates reports that contain all the property data for one or more tables, forms, queries, code modules, and other objects in your databases. The following steps show one way to use the Database Documenter. You can apply the steps to any of the objects in a database as needed.

To run the Database Documenter
  1. Open the Northwind Traders sample database.

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.
  1. On the Tools menu, point to Analyze, and then click Documenter.
  2. In the Documenter dialog box, click the tab that represents the type of database object you want to explore. Choices include Tables, Queries, and Forms. To create a report on all the objects in a database, click the All Object Types tab.
  3. Select one or more of the objects listed on the tab. To select all the objects on a tab, click Select All.
  4. Click OK.

Access creates a report that contains the data for each selected object. For example, if you run the Database Documenter against the Employees form in the Northwind database, you see properties for the overall form, for each of the sections in the form, and for each of the buttons, labels, text boxes, and other controls on the form. You also see the code modules and user permissions used by the form.


ShowExplore tables in Design view

Opening a table in Design view gives you a detailed look at the table's structure. For example, you can find the data-type setting for each field, find any input masks, or see if the table uses any Lookup fields — fields that use queries to extract data from other tables. It helps to know those facts because data types and input masks can affect your ability to find data. For example, the Find and Replace dialog box only searches for text data under some conditions, and it only searches for formatted data under other conditions.

To open a table in Design view
  1. Open the Northwind Traders sample database, and then open the Orders table in Design view.

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. Select the Orders table, and then click Design Button image.

Access opens the table in Design view.

  1. At the bottom of the Table window, click the Lookup tab.
  2. At the top of the Table window, click the field names in the Field Name column. As you click the field names, pay attention to the Lookup tab. You can see that some of the fields — CustomerID, EmployeeID, and ShipVia — use queries to retrieve data from the Employees, Customers, and Shippers tables.

Those Lookup fields affect how you run find and find-and-replace operations. For example, to run a find operation on a Lookup field successfully, you usually have to select the Search Fields as Formatted check box in the Find and Replace dialog box. If you try to run a find-and-replace operation on a Lookup field, Access displays an error message. The content of the message may vary. For example, you may see either of these messages:

  • The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
  • The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

Both messages appear when you try to run find-and-replace operations on a Lookup field. The messages themselves are not as important as the principles behind them:

  • Know your database.
  • Don't run find-and-replace replace operations on Lookup fields.

If you need to see the relationships among all the tables in a database, close the Table window, leave the database open, and go to the next section.


ShowUse the Relationships window to see relationships among tables

If you haven't already, open the Northwind Traders sample database, display the Database window, and select the Orders table. If you aren't sure how to select a table in the Database window, see the previous section.

To display the Relationships window
  • On the toolbar, click Relationships Button image, or on the Tools menu, click Relationships.

The Relationships window shows you the relationships among the all the tables in a database. If you followed the steps in the previous section, you saw that the Orders table has relationships with the Order Details, Employees, Customers, and Shippers tables. For more information about using the Relationships window, see the topic View existing relationships (MDB).

If you're comfortable creating or changing the relationships among tables, you can double-click a table to open the Edit Relationships dialog box. For more information about using the Edit Relationships dialog box, see the topic Define relationships between tables (MDB).


ShowUse the Object Dependencies task pane to see how objects use other objects

You use the Object Dependencies task pane when you need to see how database objects such as tables or forms use other objects. While Design view shows you specific details about a table, such as the data types assigned to each field, and the Relationships window shows you the relations between tables, the Object Dependencies task pane provides a visual look at all of the forms, tables, queries, macros, and code modules used by a given component.

The Object Dependencies task pane may provide you with more information than you need to find a set of records, but it illustrates a fact that you need to keep in mind. In order to manage a database, you must understand how all its components interact. This is especially true if you want to change a table or form because you usually have to add your changes to several components to make a change work.

To use the Object Dependencies task pane
  1. If you haven't already, follow the steps in the section Explore tables in Design view to open the Northwind Traders sample database, display the Database window, and select the Orders table.
  2. Right-click the Orders table, and then click Object Dependencies on the shortcut menu.
  3. To see a list of the objects that use the Orders table, click Objects that depend on me at the top of the task pane. To see a list of the objects that the Orders table uses, click Objects that I depend on.

Keep in mind that the Object Dependencies task pane works only for tables, forms, queries, and reports in an Access database. For more information about the Object Dependencies task pane, see the topic View dependency information (MDB).


Find and replace data

The following sections explain how to use the the Find and Replace dialog box and select queries to find and optionally replace data. Keep these facts in mind as you proceed:

  • When you use the Find and Replace dialog box, you can run find and find-and-replace operations against forms open in Form (normal) view and against tables and query results open in Datasheet view. You can also find blank fields and add data to them.
  • You can use wildcards to find data, but if you use them in replacement strings, Access treats the characters as literals and writes them to your database. For example, say you use the string old * to find records that contain the word “old. ” That's a valid way to find data. But if you replace each instance of “old” with a string such as new *, Access can replace the entire record with that second string if you aren't careful. For more information about wildcards, see the section Use wildcard characters in find operations.
  • When you run a find-and-replace operation against a Lookup field, Access displays an error message. To run a find-and-replace operation on the data in the Lookup field, you must run it against the source table for the data. For that reason, it helps to know the structure of your database. For information about learning the structure of a database, see the section Familiarize yourself with a database.
  • You cannot use the Find and Replace dialog box to find information in a report.
  • In addition to using the Find and Replace dialog box, you can browse records to find the strings you want by scrolling in tables and query results open in Datasheet view, and by entering record numbers in the record number box in a datasheet or form.
  • If you need to locate records in multiple tables that do not share data by using Lookup fields or relations, you must use a query. For more information about creating select queries, see the topic Create a select or crosstab query (MDB). In addition, you can not use the Find and Replace dialog box to run find-and-replace operations against query results. If you want to change data by using a query, you must create an update query. For information about creating an update query, see the topic Create an update query (MDB).

Finally, a section also explains how to use the Search Fields as Formatted check box, an option in the Find and Replace dialog box.

ShowFind and replace records in tables

The following steps use the Orders table in the Northwind Traders sample database. You can adapt the steps for use with your own data.

  1. Open the Northwind Traders sample database and then open the Orders table in Datasheet view.

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. 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.
  2. To find a string, click the Find tab. To run a find-and-replace operation, click the Replace tab.
  3. In the Find What box, type rest, or your own search string. To replace a string, enter a replacement string in the Replace With box.

 Note   Do not enter wildcard characters in the Replace With box unless you want your records to contain them.

  1. From the Look In list, select Customer or the name of the column in your database. You use the options in the list to search individual columns or a whole table.
  2. In the Match list, click Any Part of Field.
  3. Make sure that the Search Fields As Formatted check box is selected, and then click Find Next. If you searched using the sample string "rest," provided in step 5, the find operation highlights rest in every instance of text that contains those letters — "Restaurante", "Restaurant", and so on. If you continue to click Find Next, the operation cycles through the data in the column.

As you search, you can set and change some of the controls in the Find and Replace dialog box. The following table lists the controls and the implications of changing them.

Control Use Behavior
Look In list Switches between searching a column and searching an entire table The Look In list always contains the name of the table you're searching. If you search only a table, Access displays the table name but makes the Look In list unavailable. When you search a column, Access makes the Look In list available and displays the table and column names. To switch between searching tables and columns, select the desired value from the Look In list.
Match list Controls which part of a field the find operation tries to match Select Any Part of Field to search for a match on all possible values. Select Whole Field to search for information that matches your search entry exactly. Select Start of Field if you think the values you want to find reside at the beginning of a record.
Search list Changes the search direction Select Up to find records above the insertion point. Select Down to find records below the insertion point. Select All to search all records, starting from the top of the record set.
Match Case check box Finds values that match the capitalization of your search string Select the Match Case check box when you want to find records that match the capitalization of your search string.
Search Fields As Formatted check box Searches based on an input mask or format Find operations can search for data or the format applied to data. For example, you can search for Feb in fields formatted with a Date/Time input mask. This option remains unavailable until you search on a field with either a format or input mask applied. For more information about this option, see the section Understanding the Search Fields As Formatted check box.

ShowFind and replace records in forms

The following steps use the Orders form in the Northwind Traders sample database. You can adapt the steps for use with your own data.

 Note   You can not run a find-and-replace operation on a Lookup field. If you want to replace data from a form, that form must be bound to the source table.

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

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 Forms under Objects.
  6. Double-click the Orders form, or select it and click Open Button image.

Access displays the form.

  1. On the form, click anywhere in the Ship To field, and then click Find Button image. You can also click Find on the Edit menu, or press CTRL+F. The Find and Replace dialog box appears.
  2. To find a string, click the Find tab. To run a find-and-replace operation, click the Replace tab.
  3. In the Find What box, type old, or your own search value. To replace a string, enter a replacement string in the Replace With box.

 Note   Do not enter wildcard characters in the Replace With box unless you want your records to contain them.

  1. In the Look In list, click Orders to search the data in the entire table.
  2. In the Match list, click Any Part of Field.
  3. In the Search list, click All, and then click Find Next. To replace a string, click Replace. If you're sure that you have entered the correct replacement string, click Replace All, but keep in mind that you can not undo a replace operation. If you make a mistake, you will have to repeat the find-and-replace operation, find the incorrect values, and replace them with the correct values.

If you use the table and search string suggested in step 4, the find operation highlights all records that contain old, such as Old World Delicatessen, Consolidated Holdings, and so on. Move the Find and Replace dialog box so that you can see the Orders form, and continue clicking Find Next. Access cycles through the records.

As you search, you can set and change some of the controls in the Find and Replace dialog box. The following table lists the controls and the implications of changing them.

Control Use Behavior
Look In list Switches between searching a column and searching an entire table The Look In list always contains the name of the form you're searching and the name of the field you click before you open the Find and Replace dialog box.
Match list Controls which part of a field the find operation tries to match Select Any Part of Field to search for a match on all possible values. Select Whole Field to search for information that matches your search entry exactly. Select Start of Field if you think the values you want to find reside at the beginning of a record.
Search list Changes the search direction Select Up to find records above the insertion point. Select Down to find records below the insertion point. Select All to search all records, starting from the top of the record set.
Match Case check box Finds values that match the capitalization of your search string Select the Match Case check box when you want to find records that match the capitalization of your search string.
Search Fields As Formatted check box Searches based on an input mask or format Find operations can search for data, or a format or input mask applied to data. For example, you can search for Feb in fields formatted with a Date/Time input mask. This option remains unavailable until you search on a field with either a format or input mask applied. For more information about this option, see the section Understanding the Search Fields As Formatted check box.

ShowFind and optionally replace data with a select query

The steps in this section use order data from the Northwind sample database. You can change these steps as needed to fit your data.

 Note   These steps provide basic information about select queries. For more advanced help, see the topic Create a select or crosstab query (MDB).

  1. If you haven't already, 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 the string you want to find, such as Alfreds, or a string that returns records from your data.
  5. Click Run Button image. The resulting datasheet contains any records that contain the text specified in the Criteria field.
  6. As desired, manually remove or replace any unwanted data.

ShowFind and replace data with an update query

Before 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, the steps in this section have you create a select query that selects and sorts a set of records, and then create an update query to change those records. As part of that process, you leave the select query open and see how the update query changes the records.

 Note   These steps provide basic information about update queries. For more advanced help, see the topic Create an update query (MDB).

To prepare the sample data
  • If you haven't already, 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.
  • 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.

  • In the Show Table dialog box, double-click Orders and then click Close.
  • In the Orders table in the design grid, double-click ShipName.
  • Click the Sort field and select Ascending from the list.
  • Click Run Button image to run the query. The result set (the datasheet) displays six records containing Alfreds Futterkiste.
  • Leave the query open and go on to the next set of steps.
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, enter Alfreds Futterkiste, and in the Update To field, enter Contoso Foods.

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. To undo your changes, switch the values in the Criteria and Update To fields.

ShowUse the Find and Replace dialog box to find records in query results

The following steps use the Quarterly Orders by Product query in the Northwind Traders sample database. You can adapt the steps for use with your own data.

 Note   Keep in mind that when you use the Find and Replace dialog box, you can run only find operations against query results. If you want to change or remove data using a query, see Find and replace data with an update query earlier in this article, or see the topic Create an update query (MDB).

  1. Open the Northwind Traders sample database and then run the Quarterly Orders by Product query.

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 Queries under Objects.
  6. Double-click Quarterly Orders by Product, or select it and then click Open Button image.

Access runs the query and displays the result set in Datasheet view.

  1. In the results, click any of the fields in the Customer column, and then click Find Button image. You can also click Find on the Edit menu, or press CTRL+F. The Find and Replace dialog box appears.
  2. In the Find What box, type alf.
  3. In the Look In list, click Quarterly Orders by Product : Crosstab Query to search all the records returned by the query.
  4. In the Match list, click Any Part of Field.
  5. In the Search list, click All, and then click Find Next.

The find operation highlights all records that contain alf. Because you selected All in the Search list, Access cycles through all the records.


ShowFind blank fields, null values, or zero-length strings

You can find three types of blank fields in an Access database:

  • Fields formatted to display a value when they're blank. For example, a format may specify that a word such as "Unknown" appears in a field until you enter a value in that field.
  • Blank, unformatted fields, or null values.
  • Fields that contain zero-length strings. You enter zero-length strings by typing a pair of quotation marks with no spaces between them (""). When you do that, and then move the insertion point to another field, Access hides the quotation marks and the field appears to be blank. Note that you must set the Allow Zero Length property to Yes for a field in order to enter zero-length strings. Also note that you can enter zero-length strings only for the Text, Memo, and Hyperlink data types, and that those types allow zero-length strings by default. For more information about zero-length strings, see the topic Indicate nonexistent data with a zero-length string.

The following steps explain how to find each type of field.

  1. As needed, open the table that contains the values you want to find in Datasheet view. If you need help doing that, see the section Find and replace records in tables, earlier in this document.
  2. On the toolbar, click Find Button image. The Find and Replace dialog box appears.
  3. Do one of the following:

ShowFind fields formatted to show a value

  1. To find formatted values, click the Find tab. If you want to find the values and replace them with other data, click the Replace tab.
  2. In the Find What box, type the value specified by the format. To add data to the empty field, enter the new data in the Replace With box.
  3. In the Match list, click Whole Field.
  4. Select the Search Fields as Formatted check box.

ShowFind blank, unformatted fields

  1. To find fields, click the Find tab. If you want to find the fields and add a value, click the Replace tab.
  2. In the Find What box, type Null or Is Null. If you're replacing the null value with other data, enter the new data in the Replace With box.
  3. In the Match list, click Whole Field.
  4. Clear the Search Fields as Formatted check box.

ShowFind fields containing zero-length strings

  1. To find zero-length strings, click the Find tab. If you want to replace the strings with other data, click the Replace tab.
  2. In the Find What box, type a pair of quotation marks with no spaces between them (""). If you're replacing the zero-length strings with other data, enter the new data in the Replace With box.
  3. In the Match list, click Whole Field.
  4. Clear the Search Fields as Formatted check box.

ShowFind and replace quotation marks

If your find operation fails to return records containing quotation marks, remember to try selecting or clearing the Search Fields as Formatted check box.

  1. Open in Datasheet view the table that contains the quotation marks you want to find, or open the form bound to that table. For information on opening a table or form, see Find and replace records in tables or Find and replace records in forms, both earlier in this article.
  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.
  3. If you only want to find quotation marks, click the Find tab. To find and replace the quotation marks, click the Replace tab.
  4. In the Find What box, enter a quotation mark. To replace the quotation marks with another value, enter that value in the Replace With box.

 Note   You can leave the Replace With box blank if you want to remove the quotation marks entirely.

  1. From the Look In list, select the field that contains the quotation marks, or select the name of the table. The first option narrows the search. The second option searches the entire table and provides the most flexibility, but the find operation can take more time.
  2. From the Match list, select Any Part of Field. Again, this option provides the broadest possible search.
  3. From the Search list, select All to search through all the records.
  4. Select the Search Fields as Formatted check box.

 Note   If Access selects the check box automatically, accept that decision unless your find operation fails.

  1. Click Find Next to find the records. If the find operation fails, clear the Search Fields as Formatted check box and try again. If you're sure you want to replace the quotation mark with another value (or no value), click Replace. If you're confident that you're finding and replacing the correct values throughout your table, click Replace All, but remember that you can not undo a replacement operation. If you make a mistake, you must run additional find-and-replace operations that reverse your mistake.

ShowFind a specific record by scrolling in a datasheet or form

If a table or a form contains a large number of records, you can use the scroll box on the scroll bar to find a specific record.

  • Drag the scroll box up or down on the scroll bar.

A ScreenTip beneath the scroll box displays the record number for the record located next to the scroll box.

A record number in a ScreenTip

To see the record number for a given record at any time, point to the scroll box and then press and hold down the left mouse button.


ShowFind a record by entering record numbers in a datasheet or form

ShowUnderstanding the Search Fields As Formatted check box

If you followed the steps in either of the previous sections, you left the Search Fields As Formatted check box selected as you searched the Customers column of the Orders table. If you clear that check box and run the find operation on the same data, the operation does not find any records. Why? Because the data has an input mask applied.

So follow this rule of thumb: When the Search Fields As Formatted check box is selected, leave the check box alone.

At times, it may seem like the Search Fields As Formatted check box is selected for no apparent reason. For example, if you open the Orders table in Design view and you select the CustomerID field (the field that contains the customer data), you don't see a format or input mask listed on the General tab. But if you click the Lookup tab (while in Design view), you see that the CustomerID field uses a query to retrieve data from the Customers table. If you open the Customers table in Design view, you see that the source field (also named CustomerID) has a value in the Input Mask box applied: >LLLLL. That input mask is the reason the Search Fields As Formatted check box is selected.

This is another reason to familiarize yourself with your databases. For more information about doing so, and about using Design view, see the section Familiarize yourself with databases.


Use wildcard characters in find and find-and-replace operations

Wildcard characters act as placeholders for other characters during find and find-and-replace operations. You use wildcards when you know only part of the value that you want to find, and when you want to find values that match a specific pattern or start with a specific letter.

You use the same tools when working with wildcards as you do when finding and replacing data — The Find and Replace dialog box, select queries, and update queries. As you proceed, keep this rule in mind: To find wildcard characters in a database, you surround the wildcard character with brackets, such as [*]. You follow that rule when searching for all wildcard characters except exclamation points (!) and closing brackets (]).

Also, remember that the rule applies when you use the Find and Replace dialog box or run a select query, but it does not apply when you run an update query.

 Note   When you use the Find and Replace dialog box to run a find-and-replace operation , do not enter wildcard characters in the Replace With box unless you want your records to contain those characters. When you use wildcard characters in replacement strings, Access treats those characters as literals and writes them to your database.

You can use wildcard characters with fields set to the Text data type. You can also use them with other data types such as Currency and Date/Time, but you will probably need to select the Search Fields as Formatted check box for the find operation to work.

Finally, you can't search for opening and closing brackets ([]) together because Access interprets that combination of characters as a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").). If you need to find pairs of opening and closing brackets, you must enclose them in brackets ([[]]). For information about finding zero-length strings, see the section Find blank fields, earlier in this article.

ShowTable of available wildcard characters

The following table lists the wildcard characters that you use to search Access databases (.mdb files). You use these wildcards in the Find and Replace dialog box and in queries.

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", but not "saddened" or "shouldn't".
? Matches any single alphabetic character. b?ll finds "ball", "bell", and "bill", but not "billed".
[ ] 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", but not "barred".
# Matches any single numeric character. 1#3 finds "103", "113", and "123".

 Note   You also use these 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 when you run queries in an Access project. Access provides different sets of wildcards because it supports two standards for Structured Query Language — ANSI-89 and ANSI-92. The Find and Replace dialog box supports the ANSI 89 standard. Queries in an Access project support ANSI-92 because SQL Server uses that standard. For more information about those additional wildcards, see the topic About using wildcard characters. In addition, you can set and change the ANSI standard that an Access database uses. For more information about the ANSI SQL query modes, see the topics About ANSI SQL query mode (MDB) and Set ANSI SQL query mode (MDB).


ShowUse the Find and Replace dialog box to find wildcard characters

Remember to enclose the wildcard character that you want to find in brackets ([]), except when searching for exclamation points (!) or closing brackets (]).

Because the Northwind sample database does not contain wildcard characters, the steps in this section explain how to add some of those characters to the database, and then perform find operations. You can adapt these steps for use with your own data.

To prepare sample data
  1. Start the Northwind sample database and open the Orders form.

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

The Orders form opens in Form (normal) view.

  1. In the Ship To field, select a record and enter an asterisk or other wildcard character. Scroll through the records and add wildcards to records for several customer. Keep the form open and go to the next steps.
To find wildcard characters
  1. In the Orders form, click anywhere in the ShipName field, and then click Find Button image. You can also click Find on the Edit menu, or press CTRL+F. The Find and Replace dialog box appears.
  2. If you only want to find records with wildcard characters, click the Find tab. If you want to find and replace wildcard characters, click the Replace tab.
  3. 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 [*]. If you want to run a replace operation, type your replacement string in the Replace With 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, select the option that you think best applies to your data. For example, if the wildcard characters reside at the start of your records, click Start of Field. Otherwise, click Any Part of Field to return the largest possible number of results.
  6. 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 want to replace the wildcard, click Replace. If you're sure that search and replacement strings will give you correct results, click Replace All. However, remember that you can not undo the results of a find-and-replace operation.

ShowUse a select query to find and replace wildcard characters

  1. If you haven't already, 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:

A 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. As desired, 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.

ShowHow?

  • In the query design grid, click the or field directly below the Criteria field.
  • Enter 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 querying for exclamation points and closing brackets — for example, Like "*!*" Or Like "*]*"
  • 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 surround 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.

ShowUse an update query to find and replace wildcard characters

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, the steps in this section have 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, 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. 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.

  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 containing 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.
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, enter #Alfreds Futterkiste, and in the Update To field, enter 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.


ShowExamples of wildcards 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 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, and it explains how the options in the Match list can affect your results.

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.

Troubleshoot find and find-and-replace operations

The following sections explain how to solve some common problems with find operations. If you have a problem that these sections do not answer, try posting your question to one of the Access newsgroups. For more information about using newsgroups, visit the Office Discussion Groups.

ShowI can't find or replace values that I know exist.

  • If the Find and Replace dialog box doesn't return records that meet the criteria you specified, and you know the records exist, select the Search Fields As Formatted check box. You may be trying to search a Lookup field, and a find operation only works on that type of field when you select the check box.

ShowHow do I know when I'm searching a Lookup field?

  1. Select the table that you're trying to search and click Design Button image.

Access opens the table in Design view.

  1. Click the Lookup tab, and then click the field in question in the upper part of Design view.

A Lookup field looks up values from another table and stores references to those values in the current table. If the field you're searching is a Lookup field, you must select the Search Fields As Formatted check box to find the records. For more information, see the section Understanding the Search Fields As Formatted check box.

  • When you try to find data in an Access project (an Access file connected to a Microsoft SQL Server database), Access performs the operation only on the data stored on the client computer, and that computer may have reached its maximum record limit. You may be able to find the desired values by bringing all the available data to the client computer.

To do so, click the Maximum Record Limit button to the right of the navigation buttons.

The Access record buttons

Follow the on-screen instructions in the Set Maximum Record Count dialog box ( type 0) and click OK.


ShowI can't find or replace text in a field with an input mask.

You or another user may have applied an input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) to a table that already contained data, and that existing data may not meet the criteria of the input mask. Under those conditions, the Find and Replace dialog box only finds data that adheres to the input mask. To resolve this, you can remove the input mask in table Design view, use the Find and Replace dialog box to find your data, and then reinstate the input mask. Alternatively, you can modify the nonconforming data in the field so that it adheres to the criteria set by the input mask.

For more information about input masks, see the following topics:


ShowIt takes too long to find and replace data.

For the fastest searches using the Find and Replace dialog box, click Whole Field in the Match list, and then enter search strings that you think match an entire field. If you search an indexed table, click Start of Field in the Match list.

If you search the same nonindexed field repeatedly, you can speed the search process by creating an index for the field. An index is an internal table with two columns. One column contains the values in the fields that you're indexing, and the other contains the physical location of each field that contains the values. Access uses indexes in the same way that a reader uses an index in a book. It looks up an entry and goes to the location or locations that contain that entry, making the search process faster.

ShowHow?

You can create an index for a single field, or for multiple fields.

To create an index for a single field    

  1. Open the table that you want to index in Design view.
  2. In the upper portion of the window, click the field for which you want to create an index.
  3. In the lower portion of the window, click in the Indexed property box. At this point, you have two choices: Yes (Duplicates OK) or Yes (No Duplicates). Select Yes (Duplicates OK) when you need to index a field that contains many instances of a value. For example, you may have a product category that contains many different products. When you have that type of field, allow duplicates. If you select the Yes (No Duplicates), Access enforces unique values in the field because it creates an index that doesn't allow duplicates. In other words, users cannot enter a duplicate value because the index won't allow them.

To create a multiple-field index    

  1. Open the table that you want to index in Design view.
  2. Click Indexes Button image on the toolbar.
  3. In the first blank row in the Index Name column, type a name for the index. You can base the name of the index on one of the existing index fields, or use another name.
  4. In the Field Name column, click the arrow and select the first field for the index.
  5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you select all the fields you want to include in this index.

ShowI can't find the data I want when I use wildcards.

First, make sure that the data exists. For information about doing so, see the section Familiarize yourself with an Access database. If you know the data exists, make sure that you're using the correct set of wildcard characters. Access supports two different standards for Structured Query Language, ANSI-89 and ANSI-92, and each standard has its own set of wildcard characters. You use the ANSI-89 wildcards when you use the Find and Replace dialog box and when you run queries against an Access database. You use the ANSI-92 wildcards when you run queries in an Access project (an Access file connected to a Microsoft SQL Server database).

For example, say you want to find all records that start with "B". If you use the Find and Replace dialog box with an Access database, an Access project, or in queries run against an Access database, you use the ANSI-89 wildcard character: B*. But when you run a query in an Access project, you use the ANSI-92 wildcard character: B%. For more information about the ANSI-92 wildcard characters, see the topic About using wildcard characters. For more information about the ANSI SQL query modes, see the topics About ANSI SQL query mode (MDB) and Set ANSI SQL query mode (MDB).

To change the ANSI SQL query mode    

  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 ANSI-92, or clear the check box to use ANSI-89.

 
 
Applies to:
Access 2003