Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: How can I total time?

    Hello,

    Can someone tell me how to sum my start_time and end_time? I have three fields (start_time, end_time and total hours) and would like to get the total number of hours worked.

    Also, this is a database to capture volunteer work so the hours calculated usually do not exceed 8 hours. The time window could be anywhere between 7:00 AM and 5:00 PM.

    Appreciate your time. :-)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    TotalTimeInSeconds = DateDiff("s", StartTime, StopTime)
    You can then convert the amount of seconds in any format you want.

    Have a nice day!

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Sinndho,

    Thank you for your help. I have another question, how can I add this statement to my form text field? Do I place it in an event?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set the source in the control = to wahtever

    = DateDiff("h", StartTime, StopTime)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In the control source property of a new textbox, place the expression DateDiff("s", StartTime, StopTime). You could also use a calculated field in a query, then your form will see it as a normal field. To do that, just place the expression in a new column SecondsDiff: DateDiff("s", StartTime, StopTime).
    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
    Sep 2008
    Posts
    150
    I actually placed the statement in the text box and it works great! I changed the format to "h" for hours. The problem I am having is it will not total the half hour.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by mlrnet
    I actually placed the statement in the text box and it works great! I changed the format to "h" for hours. The problem I am having is it will not total the half hour.
    ?
    well in that case write a function that returns the required number.
    you will probably have to use "m" in the datediff call and then divide by 60, apply any rounding rules to get to the appropriate value

    eg
    public function CalctimeDifference(StartTime as datetime, EndTime as DateTime) as single
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2008
    Posts
    150
    Hi Healdem,

    I have a question, can I use this function in a report text box? I am currently using it in my form and it works great. I get an error when I put it in the control source for my report text box.

    =[Total Hrs]\60 & Format([Total Hrs] Mod 60,"\:00")

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is that meant to be TotalHrs ÷(divided by) 60?

    If it is, your slash is drunk.
    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

  10. #10
    Join Date
    Sep 2008
    Posts
    150
    LOL! Yes, it is meant to be TotalHrs divided by 60 and I wish I was drunk.

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by StarTrekker
    Is that meant to be TotalHrs (divided by) 60? If it is, your slash is drunk.
    I think that may have to go in my quote book
    Me.Geek = True

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    By the way, if you want to learn more about how Access handles Date/Times, I would suggest this article.
    Me.Geek = True

  13. #13
    Join Date
    Sep 2008
    Posts
    150
    Ughh!
    When I use this format in my input form it works GREAT (slashes and all), but it does not work in my report.

    =[Total Hrs]\60 & Format([Total Hrs] Mod 60,"\:00")

    When I us this format I get different totals.

    =[Total Hrs]/60 & Format([Total Hrs] Mod 60,"\:00")

    What the heck am I not doing?

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by mlrnet
    =[Total Hrs]\60 & Format([Total Hrs] Mod 60,"\:00")
    Quote Originally Posted by mlrnet
    =[Total Hrs]/60 & Format([Total Hrs] Mod 60,"\:00")
    You know those are two different statements right (check out the slash directions)? Are those copy-pasted or did you just mistype it here?
    Me.Geek = True

  15. #15
    Join Date
    Sep 2008
    Posts
    150
    I actually copied and pasted them. The backslash (\) statement works in my form. I understand that he forwardslash (/) is used for division, but it does not give me the totals like the backslash. I tried both statements in a form and the forwardslash works.

Posting Permissions

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