Calculating Time over a 24hr period


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
Worksite: Text
Discipline: Text
Resources Required: Text
Vehicles Required: Text
Start Date: Text
End Date: Text
Created by:
Created Date: Date/Time
Submitted by: Text
Submitted Date: Date/Time
Status: Text

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
Location: Text
Accommodation Deatils: Text
Distance Travelled for shift: Text
Status: 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],[txtEnd])
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
        End If
    End If
    ' 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") & _
            Format(dtmTime, ":nn:ss")
    End If
End Function

Open in new window

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
set the control Source of the textbox to Shift Duration

if the name of the textbox is txtShiftDuration, do the calculation using vba codes..


you can do this in the afterupdate event of the textboxes  [txtStart] and [txtEnd]
or using the click event of a button..
Rey Obrero (Capricorn1)Commented:
see the codes from this link

Functions for calculating and for displaying Date/Time values in Access

look for GetElapsedTime() sample function
SerinaStarAuthor Commented:
Ok I will have a read through this site. But why would the code be work in the form but not recording in the table?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

If I am reading this right, your start and end times are bound to fields in the underlying table.

However, you are using an unbound control to display Shift Duration (a calculated value).  The textbox displays the calculated shift duration, but since it is an unbound field it does not actually get stored.

And the way you are currently handling it is an accepted best practice.  Calculated values should be displayed but not stored as a general rule.
SerinaStarAuthor Commented:
Excellent thanks so much for your help. That work brillant!
SerinaStarAuthor Commented:
All is working well however after I enter a time into the form field Start Time I get a Run Time Error '94' Invalid use of Null. Is there something that I missed in the code?
Rey Obrero (Capricorn1)Commented:
you have to test for both input from the textbox

from txtStart

if me.txtEnd & "" <>"" then
end if

from txtEnd

if me.txtStart & "" <>"" then
end if

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.