?
Solved

Calculating Time over a 24hr period

Posted on 2011-09-29
7
Medium Priority
?
318 Views
Last Modified: 2012-05-12
Hi,

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
Fields:
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
Fields:
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")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
    
    
    
    
    
    
End Function

Open in new window

0
Comment
Question by:SerinaStar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36821809
see the codes from this link


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

look for GetElapsedTime() sample function
http://support.microsoft.com/?kbid=210604
0
 

Author Comment

by:SerinaStar
ID: 36825767
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?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36828800
set the control Source of the textbox to Shift Duration

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

me.txtShiftDuration=TimeDuration([txtStart],[txtEnd])

you can do this in the afterupdate event of the textboxes  [txtStart] and [txtEnd]
or using the click event of a button..
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 61

Expert Comment

by:mbizup
ID: 36829848
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.
0
 

Author Closing Comment

by:SerinaStar
ID: 36852562
Excellent thanks so much for your help. That work brillant!
0
 

Author Comment

by:SerinaStar
ID: 36857272
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36891144
you have to test for both input from the textbox

from txtStart

if me.txtEnd & "" <>"" then
  me.txtShiftDuration=TimeDuration([txtStart],[txtEnd])
end if


from txtEnd

if me.txtStart & "" <>"" then
  me.txtShiftDuration=TimeDuration([txtStart],[txtEnd])
end if

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question