MSQUERY: Using Find and Replace Feature with Data in Table

 Last reviewed: September 12, 1996
Article ID: Q124490
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

  • 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



    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: September 12, 1996
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.