-----Original Message-----
The in-between dates have to come from somewhere, so you will need to create
a table of dates, and use it in conjunction with your table that contains
the start and end dates.
1. Create a table with one field:
TheDate Date/Time
Make this field as primary key.
Save the table with the name tblDate.
2. Enter all the possible dates into this table, one per record. The
function below populates the tables with all the dates for the next 10
years. Change the dates in the function to get the maximum range of dates
you will ever need.
3. Create a query that contains both your events table, and this table. If
you see any line joining the 2 tables in query design view, delete the line:
it is the lack of a join (known as a Cartesian Product) that gives you every
possible combination.
4. Drag the fields you want from your event table into the grid. Drag
TheDate from tblDate into the grid, and enter this into the Criteria row
under this field:
Between [StartDate] And [EndDate]
Substitute your actual field names if they are not StartDate and EndDate.
The query returns a row for every date of the event.
Here is the function to help populate tblDate:
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/2013#
.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 created a query to work with my Calendar, that
checks todays events. Each event is given a start date and
an End date, but if you have multi day events, then the
middle date is missed. I have a the query detect start and
end dates, that was easy, but Im not sure how to build an
expression to detect the bit between the start and end
dates.
What would the expression be? And would I need any more
fields or could I just stick with the start and end dates
and then an expression?
Richard
.