Run Query based on date for Multiple days

[jedwards85]

I am currently working on a DB that stores a list of sales reports that get produced by the sales org in a simple table.
The reports produced regularly are classified as daily, weekly, monthly, or quarterly, and depending on the type, there is some additional information captured (i.e. for weekly reports, what day of the week they are produced).



I have successfully created a query that takes a date entered on a form by the user, and returns all reports that should be produced that particular day including the daily, weekly, monthly etc. in a report.
Now I want to be able to run this same query & report for either a range of dates entered by the user, or have the user enter one date, and have the report generate all the sales reports for the next 30 days.

Can I somehow iterate through my existing query in a loop between a begin and end date or is there a better approach to take?
The output should be arranged by date to form a forecast view such as:

Tuesday March 29, 2011
Daily Report 1
Daily Report 2
Daily Report 3
Weekly Tuesday Report 1
Weekly Tuesday Report 2
Monthly Report for the 29th

Wednesday March 30, 2011
Daily Report 1
Daily Report 2
Daily Report 3
Weekly Wednesday Report 1
Monthly Report for the 30th

[pbaldy]

Not in the query, but it wouldn't be hard to set up a loop in VBA to take two dates input by a user and run the queries for each date between them. It looks like you'll have the additional wrinkle of having to test what day it is and running different reports based on that? Check out Do...Loop in VBA help.

[jedwards85]

Thanks, that's what I was thinking, and yes i think you're right about testing for what the day is.

I'll see if I can find a similar use of loops but haven't had much luck to this point.

Would this need to create a temporary table to store the data as it iterates through the days to query or is there a better way?

[pbaldy]

Unless I've misunderstood what you're doing, no temp table should be required. You'd just place the current date from the loop into the form you already have working, and run/print/save/whatever your reports. The basics of the loop:

  Dim dteDate                 As Date

  dteDate = Me.StartDate
  Do While dteDate <= Me.EndDate
    Debug.Print dteDate
    dteDate = dteDate + 1
  Loop

That's just printing the date out to the VBA Immediate window for testing. You'd copy dteDate to your form and run your reports.