MSQUERY: Using Find and Replace Feature with Data in Table

ID: Q124490

1.00 2.00 WINDOWS kbtool xlquery

The information in this article applies to:

Microsoft Query for Windows, version 1.0
Microsoft Query for Windows 95, version 2.0

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.

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" (without the quotation marks).

4. In the Replace With box, type the text that you want to replace the

   found text with. For example, type "US" (without the quotation marks).

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:

   Executed SQL statement successfully

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

REFERENCES

For more information about the Execute SQL Command (File Menu), choose the Search button in Help and type:

    Execute SQL Command

KBCategory: kbtool KBSubcategory: xlquery

Additional reference words: 1.00 2.00 5.00 5.00c

Keywords          : xlquery 
Version           : 1.00 2.00
Platform          : WINDOWS

Last Reviewed: September 15, 1996