DateDiff Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the following in a query

ProdHrs:
(IIf(DateDiff("h",[StartTime],[EndTime])>0,DateDiff("n",[StartTime],[Endtime]),(DateDiff("n",[StartTime],[EndTime]))+(24*60)))/60

StartTime and EndTime are data entry fields in military time and are entered
at one time on the same day. I'm getting good results except in the
following type of instance:
Start Time = 16:00 and End Time = 16:30.. I'm getting 24.5 instead of .5.
It appears that if the result is less than 1 it is using the false side of
the statement instead of <0. I don't understand why this is being seen as <
0, so using the false side of the statement.??? Pls help!
 
DateDiff returns a value of 0 for this expression:

DateDiff("h",#16:00#,#16:30#)

That's because the time interval is less than one hour.

Use "n" instead of "h" in the expression, and change the logical operator to
"greater than or equal to":

(IIf(DateDiff("n",[StartTime],[EndTime])>=0,DateDiff("n",[StartTime],[Endtime]),(DateDiff("n",[StartTime],[EndTime]))+(24*60)))/60
 
Thank you very much.....

Ken Snell said:
DateDiff returns a value of 0 for this expression:

DateDiff("h",#16:00#,#16:30#)

That's because the time interval is less than one hour.

Use "n" instead of "h" in the expression, and change the logical operator to
"greater than or equal to":

(IIf(DateDiff("n",[StartTime],[EndTime])>=0,DateDiff("n",[StartTime],[Endtime]),(DateDiff("n",[StartTime],[EndTime]))+(24*60)))/60
--

Ken Snell
<MS ACCESS MVP>

neenmarie said:
I'm using the following in a query

ProdHrs:
(IIf(DateDiff("h",[StartTime],[EndTime])>0,DateDiff("n",[StartTime],[Endtime]),(DateDiff("n",[StartTime],[EndTime]))+(24*60)))/60

StartTime and EndTime are data entry fields in military time and are
entered
at one time on the same day. I'm getting good results except in the
following type of instance:
Start Time = 16:00 and End Time = 16:30.. I'm getting 24.5 instead of .5.
It appears that if the result is less than 1 it is using the false side of
the statement instead of <0. I don't understand why this is being seen as
<
0, so using the false side of the statement.??? Pls help!
Click to expand...
Click to expand...
 
Back
Top