List dates between dates

[pitfour.ferguson@mac.com]

My dbase has the start date and end date of each visit. How can I ask
Access to list the day of the week of the start (easy), end (easy) and,
more importantly, the dates of the visit itself - ie list the dates
between the start and end date. Basically I am doing a day of the week
analysis and need to know which day of the week is busiest.

[Allen Browne]

Re: List dates between dates

The dates have to come from somewhere, so create a table of dates, and you
can use a Cartesian Product query to list each date in each range.

1. Create a table with one field named TheDate, type Date/Time.
Save the table as tblDate.
You can use the function below to populate it with all the years of dates
your data will cover.

2. Create a query that uses your existing table, and tblDate.
There must be no line joining the 2 tables in the upper pane of query
design.
It is the lack of any join that gives every possible combination.

3. In query design, drag TheDate from tblDate into the grid.
In the Criteria row under this field, enter:
Between [Table1].[StartDate] And [Table1].[EndDate]
Substitute the real names for your table and fields.
This restricts it to only dates in the range.
Save the query.

You can now create a report listing all the dates from the query.

Here is the function to save typing all the dates by hand.
To use it open the Immediate Window (Ctrl+G), and enter:
? MakeDates(#1/1/2000#, #12/31/2019#)
or whatever date range you want to cover.

Function MakeDates(dtSta rt As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0). OpenRecordset(" tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pitfour.fergus on@mac.comwrote in message
news:1154429263 .653249.190560@ s13g2000cwa.goo glegroups.com.. .

My dbase has the start date and end date of each visit. How can I ask
Access to list the day of the week of the start (easy), end (easy) and,
more importantly, the dates of the visit itself - ie list the dates
between the start and end date. Basically I am doing a day of the week
analysis and need to know which day of the week is busiest.