DateDiff PM to AM the Next Day

[deefish99]

I am using DateDiff to calculate minutes between two times. If the times are
AM and PM, it works great, but if I am trying to get the elapsed time between
PM and AM, it does not calculate what I expect so I must be doing something
wrong.

Here is what I am using as formula in query:

Minutes: IIf([LogID]=[Next ID],DateDiff("n",[Logout_EST],[Next Login]),0)


If Next Login = 12:23 pm and Logout_EST is 1:01 pm, I get 38 Minutes...great.
But if Next Login = 11:49 pm and Logout_EST is 12:19 am [which is the next
day], I get 1410 Minutes...when it should be 40 minutes.

I have records for all shifts...so I need to be able to calculate the minutes
in the one IF statement for both scenarios.

Any ideas on how can I resolve this?

 

[fredg]

I am using DateDiff to calculate minutes between two times. If the times are
AM and PM, it works great, but if I am trying to get the elapsed time between
PM and AM, it does not calculate what I expect so I must be doing something
wrong.

Here is what I am using as formula in query:

Minutes: IIf([LogID]=[Next ID],DateDiff("n",[Logout_EST],[Next Login]),0)

If Next Login = 12:23 pm and Logout_EST is 1:01 pm, I get 38 Minutes...great.
But if Next Login = 11:49 pm and Logout_EST is 12:19 am [which is the next
day], I get 1410 Minutes...when it should be 40 minutes.

I have records for all shifts...so I need to be able to calculate the minutes
in the one IF statement for both scenarios.

Any ideas on how can I resolve this?

re: > I get 1410 Minutes...when it should be 40 minutes.<
Looks like it should be 30 minutes to me, not 40.

Store the date as well as the time and you won't have this problem.

? DateDiff("n",#3/6/2007 11:49 PM#,#3/7/2007 12:19 AM#)
30