-----Original Message-----
The dates must come from somewhere, and the easiest solution is to create a
table that contains all the dates. Then create a query containing both
tables, with no join. If Access gives you a line joining the tables, select
the line and delete it. It is the lack of a join (a Cartesian product) that
gives you a row for every possible combination.
In the query's Criteria under the date field from in the table of dates,
enter:
Between [Start_date] And [End_Date]
This restricts the query, so it now gives you one row for every date between
the two dates--exactly what you need for your report.
You can create the dates in the table automatically:
1. Make a new table, with just one Date/Time field named "WotDate".
2. Make this field the primary key.
3. Save the table with the name "tblDate".
4. Paste the function below into a new module.
5. Change the dates in the function to the date range you want.
6. Check that Access understands the code by choosing Compile from the Debug
menu.
7. Open the immediate window by pressing Ctrl+G
8. In the immediate window, enter:
? MakeDates()
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2000# To #12/31/2009#
.AddNew
!WotDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to the newsgroup. (Email address has spurious "_SpamTrap")
Steve said:
I have a problem with this query. I have a table with
three columns, they are 'Name', 'Start_date'
and 'End_Date'. I need to create a report with a list of
the dates that are between the 'Start_Date'
and 'End_Date'.
For Example:
Name Start_Date End_Date
Bob 7/1/2003 7/4/2003
I need to see:
Name Dates
Bob 7/1/2003
Bob 7/2/2003
Bob 7/3/2003
Bob 7/4/2003
Does anyone know how to do this?
Thanks,
Steve
.
