I am trying to calculate time in Access 2007. What I have 2 tables with two forms attached for data entry into the tables.
First table: Book Work/Shutdown
Work ID: Primary Key
Resources Required: Text
Vehicles Required: Text
Start Date: Text
End Date: Text
Created Date: Date/Time
Submitted by: Text
Submitted Date: Date/Time
Now most of these field are combo boxes that look other tables for easy reference in the form.
Second table: Add Details to a Job
ID: Primary Key
Work ID: Number
Assigned To: Text
Start Time: Text
End Time: Text
Shift Duration: Text
Details of Work: Text
Purchase Order Number: Text
Accommodation Required: Yes/No
Accommodation Deatils: Text
Distance Travelled for shift: Text
Apparoved By: Text
Approved Date: Date/Time
I have created a form that Control Sources is this table. To calculate the Shift Duration I have used code which I found on the net.
Which I have attached. In the form properties for the field 'shift duration' I have set the control source to =TimeDuration([txtStart],[
Row Source: Add Details to A Job and Control Source: Query/Table.
For the Start Time field I have set the properties to Control source: txtStart and the End Time Field to control Source: txtEnd.
Now it calculates the time duration correctly however the Start Time and End Time entries record back to the table but the Shift Duration does not.
I am really stuck with this one is this the right way to go about it or am I way off track?
Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
Optional blnShowdays As Boolean = False) As String
' Returns duration between two date/time values
' in format hh:nn:ss, or d:hh:nn:ss if optional
' blnShowDays argument is True.
' If 'time values' only passed into function and
' 'from' time is later than or equal to 'to' time, assumed that
' this relates to a 'shift' spanning midnight and one day
' is therefore subtracted from 'from' time
Dim dtmTime As Date
Dim lngDays As Long
Dim strDays As String
Dim strHours As String
' subtract one day from 'from' time if later than or same as 'to' time
If dtmTo <= dtmFrom Then
If Int(dtmFrom) + Int(dtmTo) = 0 Then
dtmFrom = dtmFrom - 1
' get duration as date time data type
dtmTime = dtmTo - dtmFrom
' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")
If blnShowdays Then
TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _