Finding and Replacing Records

When you are working with records in a large data table, you often need a way to locate specific records quickly. By using the Find feature, you can easily move to specific records within a table. After you have found records, you can also replace the text within them.

Finding a Record That Meets Specific Criteria

The Find feature allows you to search in a datasheet for records that meet specific criteria. Here's how it works:

Select the field containing the criteria for which you are searching.
Click the Find button on the toolbar. The Find and Replace dialog box appears (see Figure 2.7).

Figure 2.7. The Find tab of the Find and Replace dialog box, which you can use to search for values in a datasheet.
Type the criteria in the Find What text box.
Use the Look In drop-down list box to designate whether you want to search only the current field or all fields in the table.
Use the Match drop-down list box to designate whether you want to match any part of the field you are searching, the whole field you are searching, or the start of the field you are searching. For example, if you type Federal in the Find What text box and you select Whole Field in the Match drop-down list box, you find only entries where Ship Via is set to Federal. If you select Any Part of Field, you find Federal Shipping, Federal Express, United Federal Shipping, and so on. If you select Start of Field, you find Federal Shipping and Federal Express, but you do not find United Federal Shipping.
Use the Search drop-down list box to designate whether you want to search only up from the current cursor position, only down, or in all directions.
Use the Match Case check box to indicate whether you want the search to be case-sensitive.
Use the Search Fields as Formatted check box to indicate whether you want to find data only based on the display format (for example, 17-Jul-96 for a date).
Click the Find Next button to find the next record that meets the designated criteria.
To continue searching after you close the dialog box, use the Shift+F4 keystroke combination.

Replacing Data in a Table

There may be times when you want to update records that meet specific criteria. You can use the Replace feature to automatically insert new information into the specified fields. Here's the process:

Click within the field that contains the criteria you are searching for.
Click the Find button on the toolbar. The Find and Replace dialog box appears.
Select the Replace tab (see Figure 2.8).

Figure 2.8. The Replace tab of the Find and Replace dialog, with which you can replace table data.
Type the criteria in the Find What text box.
Type the new information (the replacement value) in the Replace With text box.
Choose values for the Look In drop-down list box, Match drop-down list box, Search drop-down list box, Match Case check box, and Search Fields as Formatted check box, as described in the "Finding a Record That Meets Specific Criteria" section of this hour.
Click the Find Next button. Access locates the first record that meets the criteria designated in the Find What text box.
Click the Replace button. Access replaces the text for the record and finds the next occurrence of the text in the Find What text box.

Repeat step 8 to find all occurrences of the value in the Find What text box and replace them. As an alternative, you can click the Replace All button to replace all occurrences at once.

You should use Replace All with quite a bit of caution. Remember that the changes you make are permanent. Although Replace All is a viable option, when you use it you need to make sure you have a recent backup and that you are quite certain of what you are doing. In fact, I usually do a few replaces to make sure that I see what Access is doing before I click Replace All.

Click Cancel when you're done.

If you are searching a very large table, Access can find a specific value in a field fastest if the field you are searching on is the primary key or an indexed field. Hours 8, "Creating Tables," and 14 "Power Table Techniques," cover primary keys and indexes.

With either Find or Replace, you can use several wildcard characters. A wildcard character is a character you use in place of an unknown character. Table 2.3 describes the wildcard characters.