Creating a result set based on interval between two dates.

[mrcasey]

I have a locationID, a start date, and a finish date. I calculate the number
of months between the two dates in a query.
So, for example:
LocationXYZ 1/1/06 1/1/07 12.

Based on this, I need to create a result set of 13 records one for each
month including the start and finish months.
So, I need to get to this:
LocationXYZ 1/1/06
LocationXYZ 2/1/06
....................
LocationXYZ 1/1/07.
I can't figure out how to do this without using VBA.
Is there a way just using queries?

 

[Allen Browne]

The dates have to come from somewhere, so you will need a table of dates to
give you that result.

1. Create a new table.
2. Enter just one field named TheDate, of type Date/Time.
3. Mark the field as primary key.
4. Save the table with the name tblDate.

Now you can type in all the dates you will ever need, one per row. Or, if
you prefer, you can use some VBA to put the dates in for you. (The function
at the end of this answer does that.)

Now you can create a query that uses both tables.
In the Criteria row under field tblDate.TheDate, enter:
Between [Table1].[StartDate] And [Table1].[EndDate]

Function MakeDates(dtStart 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

 

[mrcasey]

Perfect. Thanks.

The dates have to come from somewhere, so you will need a table of dates to
give you that result.

1. Create a new table.
2. Enter just one field named TheDate, of type Date/Time.
3. Mark the field as primary key.
4. Save the table with the name tblDate.

Now you can type in all the dates you will ever need, one per row. Or, if
you prefer, you can use some VBA to put the dates in for you. (The function
at the end of this answer does that.)

Now you can create a query that uses both tables.
In the Criteria row under field tblDate.TheDate, enter:
Between [Table1].[StartDate] And [Table1].[EndDate]

Function MakeDates(dtStart 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

Reply to group, rather than allenbrowne at mvps dot org.

I have a locationID, a start date, and a finish date. I calculate the
number
of months between the two dates in a query.
So, for example:
LocationXYZ 1/1/06 1/1/07 12.

Based on this, I need to create a result set of 13 records one for each
month including the start and finish months.
So, I need to get to this:
LocationXYZ 1/1/06
LocationXYZ 2/1/06
...................
LocationXYZ 1/1/07.
I can't figure out how to do this without using VBA.
Is there a way just using queries?