Solved

Calculating Time over a 24hr period

Posted on 2011-09-29
7
310 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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 119

Expert Comment

by:Rey Obrero
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now