# Thread: Query time expression error

1. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2007
Posts
21
=Round(DateDiff("n",#12:00:00 AM#,Format([TimeIn]-1-[TimeOut],"Short Time"))/60,2)

5. Registered User
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
•