Solved

Calculating Time over a 24hr period

Posted on 2011-09-29
7
309 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

15 Experts available now in Live!

Get 1:1 Help Now