Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    9

    Unanswered: Calculating Total Hours in Access

    I've been working with this dbase for a week and I have been searching and no luck.

    We have 2 shifts, 1st shift starts at 7:00 AM to 5:00 PM and 2nd shift 4:00 PM - 2:00 AM. I have a Timesheet Tab and fields are "Time In1", "Time Out1", "Time In2", "Time Out2".

    I am able to get the sub total for each date using this formula "=NZ([Time out1]+1-[Time In1])+NZ([Time Out2]+1-[Time In2])" in the form, query and report. But I am unable to get the total hours for sub total.

    Attached is my dbase.. Please HELP.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the datediff function if you subtract the time in 1 from timeout1 and the same for time 2 you should get a value in hours
    you can then sum on that
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    WHERE are you trying to see the result? Query? Report? Form?
    WHERE is that formula? Query? Report? Form?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2006
    Posts
    9
    Report and possibly query.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I am able to get the sub total for each date using this formula "=NZ([Time out1]+1-[Time In1])+NZ([Time Out2]+1-[Time In2])" in the form, query and report. But I am unable to get the total hours for sub total.
    And this formula gives your query a calculated field called...... something.

    In your report, you can use:

    =Sum([something])

    To give you a total.

    BTW, what is the point in using NZ if you don't supply the value to return when null??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2006
    Posts
    9
    I have attached a jpeg of what my report [Individual time Sheet Report] is giving me with "=Sum([Sub Total])" function. 19:00 + 9:00 = 4:00?? I don't get it. Sorry, I'm a newbie!
    Attached Thumbnails Attached Thumbnails Report.jpg  

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so insert a control in the report group footer

    =sum(mycolumnname)
    where mycolumnname is the name of the column containing the total hours, I think it may also work if you use the name of the control containing the total hours per day

    if that doens't work do soem calulations yourself usign a bit of vba behind the report

    declare a global variable, say totalhours
    in the reports group footer set that varaiable to zero on change of employee (or employee and week, whatever)
    add the value of the row hours worked in the detail on format event
    in the report group footer assign the value of the variable to a control
    jobsagoodun
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Posts
    214

    19+9=4

    Access does this because you are displaying hours. Once you reached 24 hours it increments for days by 1 and your hours resets to 0 so that is why it shows 4. There are several ways to do this. Maybe this will help.

    http://www.dbforums.com/showthread.php?t=1620237
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it may be sematincs, but Access is doing this becuase its a dat tiem field, had the OP used datesiff he would be working with intger values, not datetime, so simple mathmatics would work
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Agreed. Although I'd tend to use DateDiff() rather than "datesiff"

    You could format the result into Days and Hours to get it to make sense, but healdem's solution is better
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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