From a high level look here is what I am trying to accomplish. I want to export to an excel file uniquely named VARIABLE2_VARIABLE1.xls. I have one table that contains all of the individual contracts for all of the distributors world wide. I have one query that gets me the list of unique distributors and countries. The four listed below represent four unique excel files.
ex:
acme inc, USA
acme inc, Canada
Widgets inc, USA
Widgets inc, Canada

Next I have a query that gathers all of the necessary data for these
excel files. Below is the actual query:

SELECT SMS3_SAMPLE_DATA.Distributor, SMS3_SAMPLE_DATA.ListPrice,
SMS3_SAMPLE_DATA.[Net Price], Date_Adj_END.Clean_End_Date,
Date_Adj_START.Clean_Start_Date, SMS3_SAMPLE_DATA.[Quote Type],
SMS3_SAMPLE_DATA.[Contract#], SMS3_SAMPLE_DATA.[Service Level],
SMS3_SAMPLE_DATA.[Item Name], SMS3_SAMPLE_DATA.[Serial Number],
SMS3_SAMPLE_DATA.[Created By], SMS3_SAMPLE_DATA.[Ordered By],
Data_Adj_CONVERSION.Clean_Conversion_Date, SMS3_SAMPLE_DATA.[Quote
Num], SMS3_SAMPLE_DATA.[Disti PO#], SMS3_SAMPLE_DATA.[Disti Billto
Country], SMS3_SAMPLE_DATA.RES_ST1, SMS3_SAMPLE_DATA.RES_ST2,
SMS3_SAMPLE_DATA.RES_ST3, SMS3_SAMPLE_DATA.RES_ST4,
SMS3_SAMPLE_DATA.RES_CITY, SMS3_SAMPLE_DATA.RES_STATE,
SMS3_SAMPLE_DATA.RES_ZIP_CODE, SMS3_SAMPLE_DATA.RES_COUNTRY,
SMS3_SAMPLE_DATA.ORDER_NUM, SMS3_SAMPLE_DATA.STS_CODE,
SMS3_SAMPLE_DATA.Reseller, SMS3_SAMPLE_DATA.[Reseller PO #],
SMS3_SAMPLE_DATA.[RESELLER CONTACT FIRST NAME], SMS3_SAMPLE_DATA.
[RESELLER CONTACT LAST NAME], SMS3_SAMPLE_DATA.[RESELLER CONTACT
PHONE], SMS3_SAMPLE_DATA.[RESELLER CONTACT EMAIL], SMS3_SAMPLE_DATA.
[End Customer], SMS3_SAMPLE_DATA.[EU Contact FIRST NAME],
SMS3_SAMPLE_DATA.[EU Contact LAST NAME], SMS3_SAMPLE_DATA.[EU Contact
PHONE], SMS3_SAMPLE_DATA.[EU Contact EMAIL], SMS3_SAMPLE_DATA.[Address
1], SMS3_SAMPLE_DATA.[Address 2], SMS3_SAMPLE_DATA.[Address 3],
SMS3_SAMPLE_DATA.[Address 4], SMS3_SAMPLE_DATA.City, SMS3_SAMPLE_DATA.
[Postal Code], SMS3_SAMPLE_DATA.Country
FROM SMS3_SAMPLE_DATA, Data_Adj_CONVERSION, Date_Adj_END,
Date_Adj_START
WHERE SMS3_SAMPLE_DATA.Distributor=[VARIABLE1] And
SMS3_SAMPLE_DATA.RES_COUNTRY=[VARIABLE2];

This query needs to be executed for every record in the first query,
and the results of the query need to exported to excel files. For the
four above referenced distributors it should created the following
four files.

USA_acme inc.xls
Canada_acme inc.xls
USA_Widgets inc.xls
Canada_Widgets inc.xls

Thanks for any help you can provide,
Taylor

If you are moving this data through Access, it is a good idea to use a local table to store the query results since you can't export the results of a dynamic query like this. The psuedo code is:

Turn off Access Warnings
Run delete query on local table

Create connection to Excel file
Create recordset on Excel file
   Run Append Query

Turn Access Warnings Off

Export to Excel (auto or manual save)

You can take the Query parameters from a combo box on a form and then insert them into this string.

The very easiest way to do this is to import all the data in a local table, THEN run the query, then append the data to another local table, then export. Delete everything in between, of course.

Did that help? Do you need code?


mmcdonal

Look it up at: http://wrox.books24x7.com