Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Question Unanswered: Totaling Daily Truck Hours In A Report

    I work in a Quarry scalehouse and keep track of when the trucks leave the scalehouse by Date and Time Out. I need a report that will give me the total hours for each of my trucks. I've tried using the date function but that totals the times. Here's what my report looks like right now:
    Truck ID Date by Day Date Time Out
    008 Monday, January 5, 2015 1/5/2015 9:05
    008 Monday, January 5, 2015 1/5/2015 9:38
    008 Monday, January 5, 2015 1/5/2015 9:59
    008 Monday, January 5, 2015 1/5/2015 10:19
    008 Monday, January 5, 2015 1/5/2015 10:58
    008 Monday, January 5, 2015 1/5/2015 11:19
    008 Monday, January 5, 2015 1/5/2015 11:45
    008 Monday, January 5, 2015 1/5/2015 12:00
    008 Monday, January 5, 2015 1/5/2015 12:34
    008 Monday, January 5, 2015 1/5/2015 12:56
    008 Monday, January 5, 2015 1/5/2015 13:23
    008 Monday, January 5, 2015 1/5/2015 13:44
    008 Monday, January 5, 2015 1/5/2015 14:03

    If you look at my last column you will see that the truck left at 9:05, then came back for another load and left at 9:38.... I need my end report to take the difference between each time back and give me a total of hours for that day then add 1 hour to the end so we can bill the trucking hours. I thought about having it take the difference between the first time out and the last time out for each truck on a daily basis and add 1 hour to that time, but I am unable to figure out how to code the module to keep running through the recordset for each truck for each day.
    For the above truck for January 5th the report should show that Truck 008 ran 5 hrs 58 min (14:03 - 9:05 4 hrs 58 min + 1 hr)

    Here's the module that I wrote, but instead of seeing the Time Out as a time, it's seeing the Time Out as hours and it adds the time out instead of giving me the difference.

    Function GetTruckDailyTotal()

    Dim db As DAO.Database, rs As DAO.Recordset
    Dim totalhours As Long, totalminutes As Long
    Dim days As Long, hours As Long, minutes As Long
    Dim interval As Variant, j As Integer

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rs = db.OpenRecordset("tblTimeEntry")
    interval = #12:00:00 AM#
    While Not rs.EOF
    interval = interval + rs![tblTimeEntryTimeOut]
    rs.MoveNext
    Wend
    totalhours = Int(CSng(interval * 24))
    totalminutes = Int(CSng(interval * 1440))
    hours = totalhours Mod 24
    minutes = totalminutes Mod 60

    GetTruckDailyTotal = totalhours & " hours and " & minutes & " minutes"

    End Function

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    this should be 1 field [timeOut] it holds date and time (you dont need 1 field for date, and 1 field for time)

    but you need 2 fields to calc the time elapsed [timeOut] ,[timeIN]
    if you are only tracking [timeOut] then you would need vba code to scan the data and post the timeIn in order to do the calc.
    datediff("h",timeIn,timeOut)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providing you are using a datetime variable / column then:-
    Access uses a real number (IIRC a double precision) to store the date and time components
    ..the integer part stores the number of days since, IIRC 31st Dec 1899
    ..the decimal part stores the time, starting at 0, which is 00:00:00,as a proportion of the day, where
    ..1 second is represented as 0.000011574074, derived as 1 / (24 [hours in a day] * 60 [minutes in an hour] * 60 [seconds in an hour])
    ..i minute is represented as 0.0006944444, derived as 1 / (24*60)
    ..1 hour is represented as 0.04166667, derived as 1 / 24
    So 0.5 equates to 12:00:00

    providing that your dat consumers never exceed 31 (the nujmkber of days in January), you can take advanatge of this storage mechanism

    subtract the event start time from the event end time, and format the result as a time value. EG:-
    format(EndTime - StartTime,"hh:nn:ss")
    or to maintian your verbose form
    Code:
    dim EventDuration as datetime
    EventDuration = EndTime - StartTime
    format(EventDuration,"hh hours nn minutes and ss seconds")  'not sure this will work you may need to break this into 3 elements
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •