Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6

    Unanswered: Calculating time worked over 24 hour period

    My problem is I can't get my expression to return the time worked in hours and minutes, only hours. I am using the following expression:

    IIf([StartTime]<[EndTime],DateDiff("h",[StartTime],[EndTime]),24-DateDiff("h",[Endtime],[StartTime]))

    I substituted "n" for "h" and that returned a number, that when divided by 60, would not have given me the correct answer for total time worked.

    I have formatted my date and time fields as General Date.

    What do I need to change to have this work for me?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Using "n" should have worked for you...

    did you remember to change the 24 to 1440 (24*60)?

  3. #3
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6
    No I didn't do that. I'll give it a try.

    Thanks

  4. #4
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6
    Ran the expression with "n" and changed 24 to 1440 and that's giving me the answer I want.

    Thank you very much!

    Another question:
    When I click on the query to open it, it's acting like a parameter query. It opens up asking me to enter a parameter value for "n". I input "n" and up comes my query.
    I don't want to have it work this way. I prefer it to just open.
    I'm thinking I need to add another field to the query representing the "n" value, so that it doesn't keep asking for it when I open it up???

    Not sure just what I need to do.

    I've been looking for some good books that I can reference to help me learn some of this "creating expression" stuff. Can you recommend one? or a detailed help site on the web?

    Thanks again.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you post the exact expression as it is now?

    "n" is an argument to the datediff function, it shouldn't be evaluated as a parameter, that's a bit odd.

  6. #6
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6
    Hours Worked: IIf([StartTime1]<[StartTime2],DateDiff([n],[StartTime1],[Endtime2]),1440-DateDiff([n],[Endtime2],[StartTime1]))

    I noticed when I pasted this expression in that I had an error in the first portion of it.
    IIf([StartTime1]<[StartTime2],

    According to the original expression I pulled off the web help site, I should have EndTime2 in the second half of this. I went back and changed it, but it returns the incorrect time worked?

    I attached the database so you can see what I've done.


    When these guys go on shift, they count the time worked as the entire time they're away. They still need to keep track of their actual "working" hours spent on the train separate from their time spent at the bunkhouse, so hence the 2 different date and time fields.

    Thanks again for your help.
    Cheryl
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    remove the brackets from ["n"]

    the []'s explicitly tell access that it's supposed to be a field or parameter.

    just "n" should be fine.

  8. #8
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6
    I initially created the expression without the brackets.

    As soon as I switch to datasheet view, it puts them back and before it will give me datasheet view, it asks for the "n" parameter again.

    Do I need to adjust a setting in Access perhaps?

    Thanks

  9. #9
    Join Date
    Apr 2004
    Posts
    66
    I put just single quotes around it
    'n'
    and it seemed to work.

    Susan

  10. #10
    Join Date
    Apr 2004
    Location
    Yorkton, Saskatchewan
    Posts
    6
    Thanks. That's fixed the problem.

    Cheryl

Posting Permissions

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