Export Table To Csv File By Date

[jmeek]

What I would like to achieve via code on a button
From the table orders

orderDate   orderID  ref  name      amt    vat
 02/02/2014 56789    01   magdi     600    100
 22/06/2014 12122    01   magdi     654    100
 22/06/2014 65897    02   simon     250    159
 22/06/2014 12345    02   simon     156    140
 23/07/2014 56781    01   magdi     400    250
 01/08/2014 12457    01   magdi     250    900
 01/09/2014 56787    02   simon     126    500
 02/09/2014 56781    04   peter     400    250
 02/09/2014 12345    02   simon     450    122
 02/09/2014 65897    04   peter     250    231
 02/09/2014 12122    05   hargr     654    100
 02/09/2014 79457    05   hargr     254    200

I need to create csv files from the above based on unique date and
ref and with filename showing date and ref. So from the above we
should get 9 csv files in this format

02/02/2014-01.csv
22/06/2014-01.csv
22/06/2014-02.csv
23/07/2014-01.csv
01/08/2014-01.csv
01/09/2014-02.csv
02/09/2014-02.csv
02/09/2014-04.csv
02/09/2014-05.csv

Some help on how to produce the above result greatly appreciated

 

[EdFred]

You can't have file names in windows with / or \

However something like

strFilename = Format(rs![orderDate], "dd-mm-yyyy") & "-" & rs![ref] & ".csv" should work.

Then use strFilename in your export/output command.

 

[jmeek]

OK EdFred taken note about the slash
but how do I get the unique record based on date and ref ?

 

[EdFred]

OK EdFred taken note about the slash
but how do I get the unique record based on date and ref ?

Cycle through the Recordset.

Though I guess the question is, how much vba background do you have?

 

[smig]

Create a SUM query taking only the dates.
Use code to loop the dates in the query and export a query filtered by the selected date.
Use the selected date also for the file name.

 

[jmeek]

Hi Smig
I couldn't create the sum query, only a totals query. Please see the code in the attached zip file. All works well except that it doesn't export to csv based on unique values of date and ref