ACC97: Incorrect Records in Query Exported to File

ID: Q181329

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

If you modify an existing query and then export it to another file format without saving it, the expected data may not appear in the resulting file. For example, this behavior can occur when you export a query to a text file (*.txt), to Microsoft Excel 97 (*.xls), or to a dBASE file format (*.dbf).


CAUSE

When you export a query, Microsoft Access uses the SQL property of the QueryDef object to determine which records and fields should be exported. Modifications that you make to a query exist in a temporary object until you save the query. At that time, the SQL property of the QueryDef object is updated.


RESOLUTION

Save the query before you export it to another file format.


MORE INFORMATION

Steps to Reproduce Behavior


  1. Start Microsoft Access and open the sample database Northwind.mdb.


  2. Create the following query based on the Shippers table: 
    
       Query: qryExportTest
       --------------------
       Type: Select Query

       Field: ShipperID
          Table: Shippers
       Field: CompanyName
          Table: Shippers
       Field: Phone
          Table: Shippers


  3. Save the query as qryExportTest.


  4. On the Query menu, click Run. Note that the query returns three records.


  5. On the View menu, click Design View and add the following criteria to the ShipperID field: 
    
       <> 3


  6. Run the query without saving it. Note that the query returns two records.


  7. On the File menu, click Save As/Export.


  8. In the Save As dialog box, click "To an External File or Database," and then click OK.


  9. In the Save In dialog box, click Microsoft Excel 97(*.xls) in the Save As Type list.


  10. In the File Name box, type qryExportTest.xls, and then click Export.


  11. Start Microsoft Excel and open qryExportTest.xls. Note that in addition to the column names, the spreadsheet contains three records instead of the two records returned by the unsaved query.



REFERENCES

For more information about exporting, search the Help Index for "exporting data" and display the topic "Export data to another database or file format," or ask the Microsoft Access 97 Office Assistant.

Additional query words: prb results missing not there 


Keywords          : kbdta IntpOthr IsmExl5 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 26, 1999