Haivng problem with time display

[xiaodan86]

I am having an issue here which I don't know how to solve.
Hope to look for some advice.
Currently I am helping my company to develop a small program using ms
access which use to update tool performance.
My company working time per day is count from 7am to 7am.
Which means when you search for 9April record, it should give you all
the records from 8April 7am to 9April 7am.

I am already able to selects out the records base on the date I enter.
Right now I have a problem with the total time display.
Currently my total time is calculate using EndDateTime minus
StartDateTime.

But I would like to have a display something like this.
Like if a particular tool which is in process from 9Apr6:00am to
10Apr8:00am
this record will show for 8apr 9apr and 10apr(I alr done for this)
but the total time display for 8apr should be 1 hour, for 9apr should
be 24 hour and for 10apr is 1hour.

I know use the form on current, I can have a unbound text box which
compare the date and calculate out the time. But because this time is
not storing in the database, I can't have a printed copy which include
the time base on the date i select.

Is there anywhere to solve this problem?

 

[Allen Browne]

There are 2 parts to this problem:
a) Handling days that start from 7am instead of midnight.
b) Generating a record for each day between a start and end date.

You need a table with a record for each date you need to cover.
Each record needs to be for 7am, since that's when your day starts.
You can use the function below to enter the dates.
For example, to enter all dates from 1/1/2007 to 1/1/2020, enter this in the
Immediate Window (Ctrl+G):
? MakeDates(#1/1/2007#, #1/1/2020#)

Now create a query with both tables (tblDate and your original table). There
must be no line joining the tables in the upper pane of query design. Drag
tblDate.TheDate into the grid. In the Criteria row under this field, enter:
Between Table1.StartDateTime And Table1.EndDateTime
substituting your table name. The query now creates a record for each date.

You can now type an expression into the Field row to calculate the number of
hours per day. Assuming you want hours and fractions of an hour, it will be
something like this:
Hours: DateDiff("n",
IIf(Table1.StartDateTime < tblDate.TheDate,
tblDate.TheDate, Table1.StartDateTime),
IIf(Table1.EndDateTime > tblDate.TheDate + 1,
tblDate.TheDate + 1, Table1.EndDateTime)
) / 60

The expression chooses:
- whichever is the later of StartDateTime and TheDate,
- whichever is the earlier of EndDateTime and TheDate + 1,
- calculates the difference in minutes,
- divides by 60 to get hours and fractions of an hour.

And here is the code to populate the 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 = DateAdd("h", 7, dt)
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function