Need to create dates & hours

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to write a query where given a start and stop date, the query results
would produce a value for every day and hour inbetween. We bill hourly, so
on our statement detail page my boss wants me to list each hour with a
corresponding dollar amount. A job can cross over days, so it also needs to
recognize any days inbetween. Please advise as to how I can set this up.

So far I've created a table of hours (24 rows) and have been able to create
a query that can grab the inbetween hours for one day. But I can't figure
out how to get a query to recognize more than one day.

Thank you!
 
The dates an hour have to come from somewhere.

You already have a table of hours. Good.
I will assume it contains one field of type Number, size Long Integer, named
TheHour, marked as primary key. The 24 records should contain the values 0
to 23. I will assume this table is named tblHour.

Now you need another table of dates. One field of type Date/Time, named
TheDate, marked as primary key. Save as tblDate. To save typing them all by
hand, use the function below to populate it with all the dates for the
period you must examine, e.g. 2006 to 2010.

Let's assume your data is in a table named tblTimeSheet, with fields:
StartDateTime Date/Time date and time this entry begins
EndDateTime Date/Time date and time this entry ends

Create a query that uses all 3 tables. There should be *no* joins between
them in the upper pane of query design. Type this expression into the Field
row:
DateAdd("h", tblHour.TheHour, tblDate.TheDate)
Now in the Criteria row under this, enter:
Between tblTimeSheet.StartDateTime And tblTimeSheetEndDateTime

How it works: The query with no joins gives every possible combination of
records. This creates a record for every combination of timesheet entry,
date, and hour. You now limit that result by selecting only the records
where the value is in the range of the timesheet entry.

Make sure the date and time fields in tblTimeSheet are indexed. This is
going to generate a lot of records!

Here's the function to populate TheDate table:

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
 
Back
Top