Export to excel using same query many times

[Guest]

Ok here's the situation, sorry it is so lengthy.
I have a form that takes the starting and ending date for a query. This
query groups defects by type and counts them for that date range. The query
will then spit out some 300 records with the defect description in the first
field and the number of defects matching that description in the second
field.
I am trying to make an excel spreadsheet that has a running total for every
day since 1/1/2006 to today.
Now I could start at 1/1/2006 to 1/1/2006 and run the query, export the data.
Then run 1/1/2006/ to 1/2/2006, run the query, export the data.
1/1/2006 to 1/3/2006, query, export... etc.
However, I'm trying to write a macro that will do all this for me.
I know that I can use a few loops to do this but I'm having a very hard time
following examples of how to do it. I have some code that I copied from
another part in the database that someone else did previously and tried to
change it to suit my purposes but it was to no avail.
Please help

 

[Guest]

Since I seldom use macros, I don't know if you can do it there, but as a code
solution, you could use a Do Loop based on the date:

Dim dtmReportDate As Date

dtmReportDate = #1/1/2006#
Me.txtReportDate = dtmReportDate

Do While dtmReportDate <= Date()
'Do your TransferSpreadsheet Here
dtmReportDate = DateAdd("d",1,dtmReportDate)
Me.txtReportDate = dtmReportDate
Loop

Now, the issue will be that you will need to be able to pass the date
parameter to your query. Since queries cannot see VBA varialbes, but they
can see controls on forms, you will need to run this from a form and use the
form control reference in your query.
In the criteria for your date field:
[Forms]![FormName]![txtReportDate]