Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    21

    Unanswered: Query time expression error

    I'm getting an error in my report when using this expression...

    TotalHours: Sum(Round(DateDiff("n",#12:00:00 AM#,Format([TimeIn]-1-[TimeOut],"Short Time"))/60,2))

    Does something look wrong with it?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You have a few things being mixed in here, and Access does not understand what you want. Actually, neither do I. You seem to want the number of minutes between Midnight and ?????, but when you subtract TimeOut from TimeIn and subtract 1 from that (is that for lunch), then whatever this difference is in time, you want to subtract that from Midnight. Why? Plus, because you have used Format(.....), Format will return a STRING, rather than a Date/Time datatype which is what you need in DateDiff.

    Could you explain in words what you want this formula to calculate? Thanks!

  3. #3
    Join Date
    Oct 2007
    Posts
    21
    basically the sum of the total hours (taking the difference of time out and time in)

    I have Round(DateDiff("n",#12:00:00 AM#,Format([TimeIn]-1-[TimeOut],"Short Time"))/60,2 running in my form, for a display of the total hours... and it seems to calculate the numbers correctly. Mainly going from one evening to the morning of the next day... say 23:00 hours is time in and 02:00 hours is time out... it will properly calculate assuming its the next day without inputting the date.

  4. #4
    Join Date
    Oct 2007
    Posts
    21
    =Round(DateDiff("n",#12:00:00 AM#,Format([TimeIn]-1-[TimeOut],"Short Time"))/60,2)

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Does the report have the equal sign "=" in the front of the expression like you have in the form? If this is working in a form, I really don't know why it would not work in a report. Are you sure that [TimeIn] and [TimeOut] are on the report? I'm sure if this is working on the form, these two fields must be on the form, but are they on the report?

Posting Permissions

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