[AccessD] Strange date question

On 15 Mar 2005 at 21:59, Susan Klos wrote:

> No!  I think I could figure that out.  How many days in the month had an
> event? i.e. between 1/3/ and 1/11, there were events on 6 days.  Event 1, 2,
> 3 and 4 occurred between 1/3 and 1/8 and that would be 5 days.  Event 5
> occurred for 1 day 1/10 - 1/11.  That would be 1 day.  No events occurred
> between 1/7 and 1/10.  So where there are 8 days when events could have
> occurred they only occurred on 6 of those 8 days.  I thought about somehow
> placing the events on a calendar and counting only the days which were not
> null.  But, I don't know how to do that.  Any thoughts?
> 

>Event 1  Beginning Date 1/3/05   Ending date 1/3/05
>Event 2  Beginning Date 1/3/05   Ending date 1/4/05
>Event 3  Beginning Date 1/3/05   Ending date 1/8/05
>Event 4  Beginning Date 1/6/05   Ending date 1/7/05
>Event 5  Beginning Date 1/10/05 Ending Date 1/11/05

The way I read it, 

There were nine possible event days (3rd,4th,5th,6th,7th,8th,9th,10th,11th)

Events 1 - 4 happend on six days (3rd,4th,5th,6th,7th,8th)
Event 5 happened on two other days (10th and 11th)
so there were eight days with events out of the 9 (the only day without an 
event was 9th. 

Anyhow, here's one solution.

Step 1.  Create temporary table tblTempDates with a single DateTime field 
called EventDate.

Step 2 create a Form frmDates with two text boxes txtStartDate and 
txtEnddate and as button.

Step 3: Create a query (paste this into the SQL view) called "qryEventDays"

SELECT tblTempDates.EventDate
FROM tblTempDates, tblEvents
WHERE (((tblTempDates.EventDate) Between [begindate] And [enddate]) AND 
((tblEvents.EndDate)>=[Forms]![frmDates]![txtStartDate]) AND 
((tblEvents.BeginDate)<=[Forms]![frmDates]![txtENdDate]))
GROUP BY tblTempDates.EventDate;

Step 4: Put this code in the command button's On_click event in the form:

Dim lngLoopcount as long
Dim strSQL as string

"Build the calendar for the period
CurrentDb.Execute "Delete * from tblTempdates"
For lngLoopcount = txtStartDate to txtEnddate
   strSQL = "INSERT INTO tblTempDates ( EventDate ) SELECT " & lngLoopcount 
   CurrentDb.Execute strSQL
Next

'Get the event days
Msgbox "There were events on " & Dcount("*","qryEventDays")









-- 
Stuart