This topic explains how to use the Find and Replace dialog box to find and optionally replace data in an Access database.
If you need information about other ways to update data, such as using update and append queries, see the article Update the data in a database.
What do you want to do?
Learn about using the Find and Replace dialog box
You use the Find and Replace dialog box when you want to find and optionally replace small amounts of data, and when you are not comfortable using a query to find or replace data. The dialog box resembles the Find tools that you see in other programs, but it contains some features that make it more useful for searching relational databases. For example, you can search on a format applied to data, and you can choose to match part or all of the data in a field.
Remember these facts as you proceed:
-
The dialog box treats your search string as a pattern and returns all records that match the pattern. For example, suppose you have a datasheet (either a table or a query result) that includes information about customers, suppliers, products, and order dates. If you search on "mar", the find operation returns all instances of "market", "March", and any other records that match the pattern you specified.
-
The dialog box searches only one table at a time; it doesn't search the entire database. If you open the dialog box from within a form, you search the table that underlies that form.
If you need to use the dialog box to search multiple tables, you can create a select query to gather the desired data, and then search the query results. Steps later in this section explain the basics of creating select queries and searching the results.
-
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.
-
You can 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 by using "Unknown" as your search string.
Understanding the Search Fields as Formatted check box
The Find and Replace dialog box provides an option called the Search Fields As Formatted check box. As you go through the steps in this topic, you will see that Access sometimes selects the check box automatically; so at times, you must select or clear the check box yourself. Proper use of the check box can help ensure that your search operation succeeds.
As you proceed, follow this rule of thumb: When Access selects the Search Fields As Formatted check box, leave the check box alone. If you clear the check box, your search operation probably will not return any results.
That behavior may be confusing at first, because Access does not provide any visual cues or other indicators to indicate when you should select or clear the check box. You might wonder what logic drives Access to select or clear the Search Fields As Formatted check box. The answer is input masks. An input mask is a type of formatting, and when Access encounters one, it selects the check box.
Also, remember that if you search a lookup field — a field that uses a query to retrieve data from another table — and the source field in that second table has an input mask applied, Access enables the Search Fields As Formatted check box.
About the differences between finding, sorting, and filtering
Finally, remember that finding is not the same process as sorting or 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 the 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.
The following sets of steps explain how to use the Find and Replace dialog box.
Find and Replace dialog box control reference
As you search for and replace data, you frequently set and change the controls in the Find and Replace dialog box. However, remember that doing so can cause your find or replace operations to return unwanted data or fail entirely. 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 are 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 value you want from the Look In list. To search a column that is not in the list, go to the open datasheet or form and select the column you want, and then return to the Find and Replace dialog box and run your search. |
|
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 cursor. Select Down to find records below the cursor. 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. |
Find and replace data in a table
To follow these steps, you must open your tables in Datasheet view.
Note: You can not run a find-and-replace operation on a Lookup field. If you want to replace data from a control (such as a text box) on a form, that control must be bound to the source table.
Find and replace data
-
In your table, select the field (column) that you want to search.
Note: If you want to search the entire table, follow the alternate procedure in step 5.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
Keyboard shortcut Press CTRL+F.
This figure shows the command:
The Find and Replace dialog box appears.
-
To find data, in the Find and Replace dialog box, click the Find tab. To run a find-and-replace operation, click the Replace tab.
-
In the Find What box, type your search string. To replace data, 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.
-
Optionally, use the Look In list to change the field that you want to search, or to search the entire table instead.
By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
-or-
If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
-
Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
-
Make sure that the Search Fields As Formatted check box is selected, and then click Find Next.
Find and replace data in a form
If you don't have the necessary permissions to view and search a table, or if you don't want users seeing some of the data in a table, you can run find and replace operations on forms open in both Form view and Layout view. Both operations search the underlying table that is bound to the form.
Note: You cannot run a find-and-replace operation on a Lookup field. If you want to replace data from a control (such as a text box) on a form, that control must be bound to the source table.
Find data in a form
-
Open the form that you want to search in Form view or Layout view.
-
Select the control that contains the information that you want to search.
Note: If you want to search the entire table, follow the alternate procedure in step 5.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
–or–
Press CTRL+F.
The Find and Replace dialog box appears.
-
To find data, click the Find tab. To run a find-and-replace operation, click the Replace tab.
-
In the Find What box, type your search value. To replace data, type 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.
-
Optionally, use the Look In list to change the field that you want to search, or to search the whole underlying table instead.
By default, the Look In list contains the name of the control that you selected in step 1. If you want to search the table that underlines the form, select the name of the table from the list.
-or-
If you decide to select another control (the equivalent of selecting another table field), click the control on the form at any time. You do not need to close the dialog box.
-
Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
-
In the Search list, click All, and then click Find Next. To replace a string, click Replace. If you are sure that you have entered the correct replacement string, click Replace All, but keep in mind that you cannot 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.
Find data in a query result set
The following steps explain how to use the Find and Replace dialog box to find records in the result set returned by a select query. Remember that you can run only find operations against query results.
If you want to change or remove data by using a query, see the articles Create and run an update query and Create and run a delete query.
Create a select query
-
Open the database that contains the records you want to find.
-
On the Create tab, in the Other group, click Query Design.
-
Select the table or tables that contain the records you want to find and click Add, and then click Close.
The table or tables appear as one or more windows in the upper section of the query design grid, and the windows list all the fields in each table. The following figure shows the designer with a typical table:
-
Double-click the fields that you want to find. The selected fields appear in the Field row in the lower section of the query designer.
You can add one table field per column in the lower section.
To add all the fields in a table quickly, double-click the asterisk (*) at the top of the list of table fields. The following figure shows the designer with all fields added.
-
Optionally, you can enter one or more criteria in the Criteria row of the design grid.
Doing so can reduce the number of records that the query returns and make it easier to find your data. The following table shows some example criteria and explains the effect they have on a query.
|
Criteria |
Effect |
|
> 234 |
Returns all numbers greater than 234. To find all numbers less than 234, use < 234. |
|
>= "Callahan" |
Returns all records from Callahan through the end of the alphabet |
|
Between #2/2/2006# And #12/1/2006# |
Returns dates from 2-Feb-06 through 1-Dec-06 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotes (') instead of pound signs. Example: Between '2/2/2006' And '12/1/2006' |
|
Not "Germany" |
Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)". |
|
Not "T*" |
Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk. |
|
Not "*t" |
Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk. |
|
In(Canada,UK) |
In a list, finds all records containing Canada or UK. |
|
Like "[A-D]*" |
In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk. |
|
Like "*ar*" |
Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk. |
|
Like "Maison Dewe?" |
Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark. |
|
#2/2/2006# |
Finds all records for February 2, 2006. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/2006'). |
|
< Date() - 30 |
Returns all dates more than 30 days old. |
|
Date() |
Returns all records containing today's date. |
|
Between Date() And DateAdd("M", 3, Date()) |
Returns all records between today's date and three months from today's date. |
|
Is Null |
Returns all records that contain a null (blank or undefined) value. |
|
Is Not Null |
Returns all records that contain a value. |
|
"" |
Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field may require a fax number, but some of your customers may not have fax machines. In that case, you enter a pair of double quotation marks with no space between them ("") instead of a number. |
-
On the Design tab, in the Results group, click Run.
Verify that the query returns the records you want. As needed, you can select unwanted fields and press DELETE to remove them, you can drag additional fields to the design grid, and you can change your criteria until you are satisfied with the query results.
-
Go to the next steps.
Find data in the result set
-
In the results, click the field (the column) that you want to search.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
–or–
Press CTRL+F.
The Find and Replace dialog box appears.
-
In the Find What box, type your search string.
-
Optionally, use the Look In list to change the field that you want to search, or to search the entire table instead.
By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
-or-
If you decide to select another column, click the desired column in the datasheet for the table. You do not need to close the dialog box.
-
Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
-
In the Search list, click All, and then click Find Next.
The find operation highlights all records that contain your search string. Because you selected All in the Search list, Access cycles through all the records.
Find wildcard characters
You can use wildcard characters in find and find-and-replace operations, but you must use them carefully. Remember these rules:
-
When you use the Find and Replace dialog box to search for wildcard characters, you must 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 (]).
-
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 on old * and replace all records that match that pattern with new *, Access writes "new *" to all the records that matched your search string.
Find wildcard characters
-
Open the table, query result set, or form. You must open tables and result sets in Datasheet view, and you must open forms in Form view.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
-or-
Press CTRL+F.
The Find and Replace dialog box appears.
-
If you only want to find records that contain wildcard characters, click the Find tab. If you want to find wildcard characters and replace them with other data, click the Replace tab.
-
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.
-
Optionally, use the Look In list to change the field that you want to search, or search the entire table instead.
By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
-or-
If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
-
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.
-
Ensure 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 are sure that search and replacement strings will give you correct results, click Replace All. However, remember that you cannot undo the results of a find-and-replace operation.
Examples 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 strin g |
Match list settin g |
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.
In other words, even though you are 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". Access matches any text that precedes the characters enclosed in brackets, so you may see unwanted results. |
Find quotation marks and null or blank values
You can use the Find and Replace dialog box to find quotation marks, plus several types of blank values.
-
Fields formatted to display a value when they are 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 cursor to another field, Access hides the quotation marks and the field appears to be blank. Remember that you can enter zero-length strings only in fields set to the Text, Memo, and Hyperlink data types, and that those types allow zero-length strings by default.
Find quotation marks
-
Open the table in Datasheet view, or open the form bound to that table.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
-or-
Press CTRL+F.
The Find and Replace dialog box appears.
-
If you only want to find quotation marks, click the Find tab. To find and replace the quotation marks, click the Replace tab.
-
In the Find What box, type 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.
-
Optionally, use the Look In list to change the field that you want to search, or search the entire table instead.
By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
-or-
If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
-
Optionally, from the Match list, select Any Part of Field. This provides the broadest possible search.
-
In the Search list, select All to search through all the records.
-
Select the Search Fields as Formatted check box.
Note: If Access selects the check box automatically, accept that decision unless your find operation fails.
-
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 are sure you want to replace the quotation mark with another value (or no value), click Replace. If you are confident that you are finding and replacing the correct values throughout your table, click Replace All, but remember that you cannot undo a replacement operation. If you make a mistake, you must run additional find-and-replace operations that reverse your mistake.
Find blank values
-
As needed, open the table in Datasheet view that contains the values you want to find.
-
In the table, select the field that contains the blank values that you want to find.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
-or-
Press CTRL+F.
The Find and Replace dialog box appears.
-
Do one of the following:
Find blank fields that are formatted to show a value
-
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.
-
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.
-
In the Match list, click Whole Field.
-
Select the Search Fields as Formatted check box.
-
Click Find Next.
Find blank, unformatted fields
-
To find fields, click the Find tab. If you want to find the fields and add a value, click the Replace tab.
-
In the Find What box, type Null or Is Null.
If you are replacing the null value with other data, enter the new data in the Replace With box.
-
In the Match list, click Whole Field.
-
Clear the Search Fields as Formatted check box.
-
Click Find Next.
Find fields containing zero-length strings
-
To find zero-length strings, click the Find tab.
If you want to replace the strings with other data, click the Replace tab.
-
In the Find What box, type a pair of quotation marks with no spaces between them ("").
If you are replacing the zero-length strings with other data, enter the new data in the Replace With box.
-
In the Match list, click Whole Field.
-
Clear the Search Fields as Formatted check box.
-
Click Find Next.
-
