Report in date range to show each day in range

  • Thread starter Thread starter patricia
  • Start date Start date
P

patricia

I have a database set up to track an employee calendar.
When I run a report to show the schedule the report will
prompt you to enter in the [Start Date] and [End Date]
that you want to view. How do I get the report (report
header) to print each day between the start and end date.
Example: If I enter in 03/08/04 to 03/12/04 - and group by
Start Date - how can I get the header to print/go to
03/09/04, 03/10/04, 03/11/04, etc....regardless if there
is anyone scheduled (I want it to show if there is no
schedule).
 
The dates have to come from somewhere, so create a table that contains all
the dates. Include the table in your query, no join, limited to the date
range you want, and the query gives all dates in the range.

1. Create the table named "tblDate", with one field named "TheDate",
Date/Time Type. Mark it as primary key. Save. Run this code to fill it with
dates for the next 5 years:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2009#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

2. Create a query into tblDate. Drag TheDate into the output grid, and in
the Criteria row,enter:
Between [Start Date] And [End Date]
Save the query.


3. Create another query using the query from step 2 as a source "table", as
well as your employee calendar table. Join them on the date fields. Use this
query as the source for your report.

Hint:
Although not required, it is a good idea to declare your 2 parameters for
the query at step 2: Parameters on Query menu.
 
Back
Top