Microsoft KB Archive/124490

= Using Find and Replace Feature with Data in Table =

Article ID: 124490

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Query 2000

-



This article was previously published under Q124490



SUMMARY
Although Microsoft Query does not have a Find and Replace feature, you can use the SQL language to replace specific field information in a table in Microsoft Query. In some cases, you can also open the table in Microsoft Excel and replace data in the table. The following information describes the methods that you can use to do this.



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.  In Microsoft Query, choose Execute SQL from the File menu. In the Execute SQL dialog box, choose the Data Sources button. From the Available Data Sources list, select the data source that you want to use, such as NWind, and then choose Use. In the SQL Statement box, type the following:

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

WHERE country = 'USA'

 Choose the Execute button.

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

Executed SQL statement successfully


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

