Calculating Time without entering date - into next day 1

[wz]

I would like to calculate time without the end user entering a date. I am using (in a query)

DateDiff("n",[StartTime],[StopTime])-[BreakTime])

works perfect - except sometimes the production line runs night shift and instead of a normal:
08:00 (start) 11:00 (stop) 15 (breaktime) = 165 (minutes)

they work:
23:00 (start) 2:00 (stop) 15 (breaktime) = ? (should be 225 minutes)

since it goes into the next day it is not calculating properly. I do not want the end user to enter date...any help?

 

[nobull613]

Assuming that the Stop time always occurs after the Start time (hate to assume) you could try:

iif(DateDiff("n",[StartTime],[StopTime])<0,DateDiff("n",[StartTime],[StopTime])-[BreakTime]+1440),DateDiff("n",[StartTime],[StopTime])-[BreakTime]))

You're probably getting a negative value for your result when you cross midnight. Adding 1440 minutes helps to correct that, if no date is available in the field.

Hope that helps.

 

[wz]

Thank you so much!!!

it works perfect!!! thanks, wz