DateDiff Null Values

  • Thread starter Thread starter DM - NPS
  • Start date Start date
D

DM - NPS

I have a start time (StartTime1) and an end time (Endtime1) and would like to
subtract the 2 values using the DateDiff command. It works, however in a few
cases the end time is missing so when it does the calculation I get an #Error
in the field. How do I get the calculation to report a null value if the
starttime1 or endtime1 is missing? I tried using the code below but it did
not work.

Difference: (Nz((DateDiff("s",[startTime1],[endTime1]))))
 
use iif:

Difference: iif( endTime is null, null, datediff( "s", startTime,
endTime) )


That assumes startTime is never null.


iif, within a Jet query, execute ONLY the required "arguments" (technically,
it is not a FUNCTION, but a STATEMENT, since a function always evaluate each
and every argument before doing any real work), so, here up, if endTime is
null, the DateDiff would never be executed. In the same spirit, you can
try, ***in a Jet query***:


iif( true, 24, 1 / 0 )


and you should not get an error (division by zero), since the division does
not have to be evaluated.



Vanderghast, Access MVP
 
Thanks vanderghast for the help and the explaination. Works now!

vanderghast said:
use iif:

Difference: iif( endTime is null, null, datediff( "s", startTime,
endTime) )


That assumes startTime is never null.


iif, within a Jet query, execute ONLY the required "arguments" (technically,
it is not a FUNCTION, but a STATEMENT, since a function always evaluate each
and every argument before doing any real work), so, here up, if endTime is
null, the DateDiff would never be executed. In the same spirit, you can
try, ***in a Jet query***:


iif( true, 24, 1 / 0 )


and you should not get an error (division by zero), since the division does
not have to be evaluated.



Vanderghast, Access MVP



DM - NPS said:
I have a start time (StartTime1) and an end time (Endtime1) and would like
to
subtract the 2 values using the DateDiff command. It works, however in a
few
cases the end time is missing so when it does the calculation I get an
#Error
in the field. How do I get the calculation to report a null value if the
starttime1 or endtime1 is missing? I tried using the code below but it
did
not work.

Difference: (Nz((DateDiff("s",[startTime1],[endTime1]))))
Click to expand...
Click to expand...
 
Back
Top