Using Find and Replace Feature with Data in Table

MORE INFORMATION

Using Microsoft Excel

If you can open the table in Microsoft Excel, (for example, if the table is a dBASE IV table), you can use the Replace feature in Microsoft Excel to replace field information. To replace information in your table using this method, do the following:

1. Open the table in Microsoft Excel.
2. From the Edit menu, choose Replace.
3. In the Find What box, type the text that you want to replace. for example, type USA.
4. In the Replace With box, type the text that you want to replace the found text with. For example, type US.
5. Choose Replace All.
6. Save and close your table file.

Using SQL Statement

If you cannot open the table in Microsoft Excel, use the following method to replace field information in the table.

The following example assumes that you want to locate all of the records in the table Customer, using the NWind data source, where the Country field contains the data USA and replace this data with US. In this example, CUSTOMER.DBF is located in the c:\windows\msapps\msquery directory.

1. In Microsoft Query, choose Execute SQL from the File menu.
2. In the Execute SQL dialog box, choose the Data Sources button.
3. From the Available Data Sources list, select the data source that you want to use, such as NWind, and then choose Use.
4. In the SQL Statement box, type the following:

   UPDATE c:\windows\msapps\msquery\customer.dbf SET country='US'
   WHERE country = 'USA'

5. Choose the Execute button.

If the data is successfully replaced, you receive the following message:

1. In the Execute SQL dialog box, choose Cancel.

Note that if the table is open when you perform the steps above, choose Query Now from the Records menu to update the data.