Solved

Calculating Time over a 24hr period

Posted on 2011-09-29
7
312 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
  • 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 500 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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