Access to Excel Problem

Question

I have an Access query that I run multiple times, changing one criteria each time.  When I run this query for the first time and export it to excel, it exports the correct data.  When I run it again, changing one criteria and then export it to excel, it appears that it saves the data from the previous query.  The data in the access query is correct but the data in the excel file is from the previous query.  This happens even if I give the excel fil another name.  I am running the export without the 'Export Data with Formatting and Layout' box checked.

Answer 1

Hi Paul,

I just tested and reproduce the same behaviour you've described so I took a closer look.

In order to export the correct data once you make changes to your query, you must save your query changes. After you hit save, the correct data will then export to excel.

I looked at this behaviour in Access 2003 and Export was greyed out and not even available until you saved your query design changes. In Access 2007 and 2010, the Export option is not greyed out after making design changes which could cause confusion. I will report this behaviour to the Access developers for them to take a look at.

Please let us know if saving your design changes prior to exporting to excel resolves your issue.

Thanks!

Kathy

Answer 2

I am exporting the report to excel

I get the following options

export data with formatting  and layout ticked.

however the format in my excel is wrong.

if type in heade report as test , I get in excel as

Answer 3

Great news Paul! Thanks for the confirmation.

Answer 4

Kathy,

Saving the design changes prior to executing the query and exporting the data seems to work.  Also, if you check the 'Export Data with formatting and layout box, the correct data is exported.

Thanks,

Paul