Query Time Duration when endtime is midnight 1

[dbar10]

I am calculating shift duration from [Starttime] and {Endtime]. Everthing is great until my endtime is Midnight. Then I get negative value hh:mm. If a person starts work during the midnight hour and ends at say 7AM, I get the wrong value. Here's the query I have so far but I need help rewriting this to include the solution for this problem with the ending at midnight or starting during the midnight hour.

SELECT MasterSchedMain.MasterSchedID, MasterSchedMain.ClientID, State.State, MasterSchedMain.AuthID, MasterSchedMain.JobID, MasterSchedMain.ProgramID, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate, MasterSchedMain.Starttime, MasterSchedMain.Endtime, (DateDiff("n",[Starttime],[Endtime])/60) AS Duration, [Duration]/0.25 AS Units, MasterSchedMain.MasterCancel, MasterSchedMain.MastCancelDate, MasterSchedMain.PayBill
FROM (State INNER JOIN [Client Table] ON State.StateID = [Client Table].State) INNER JOIN MasterSchedMain ON [Client Table].ClientID = MasterSchedMain.ClientID
WHERE (((MasterSchedMain.ClientID)=[Forms]![Authorization Client Form]![ClientID]) AND ((MasterSchedMain.MasterCancel)=No))
ORDER BY MasterSchedMain.MasterSchedID DESC;

Thanks for you help

 

[GingerR]

Hi--what is the FORMAT of your time data? I made a little table and made the StartTime and EndTime be "Medium Time" format, and your formula worked fine.

StartTime	EndTime	Duration
12:00 AM	7:00 AM	7
 1:00 AM	6:00 AM	5
12:00 PM	7:00 PM	7

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dbar10]

Thanks Ginger. The problem I am having is with start time before midnight and then ending at midnight, like 4:00 PM till 12:00 AM. It then calculates backwards to a negative hh:mm. I guess thats the only time I have a problem. I am using Medium time as well. Any ideas?

 

[PWise]

Well you need to calculate the date and time

datediff("n",#7/20/09 4:00pm#,#7/21/09 12:00am#)/60

 

[randy700]

I haven't tested this, but using absolute values might do the trick. Something like...

[b][COLOR=red]Abs([/color][/b](DateDiff("n",[Starttime],[Endtime])/60) AS Duration[b][COLOR=red])[/color][/b]

Randy

 

[PHV]

What about this ?
(DateDiff("n",[Starttime],[Endtime]+IIf([Endtime]<[Starttime],1,0))/60) AS Duration

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886