DateDiff Null Values

[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]))))

 

[vanderghast]

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]

Thanks vanderghast for the help and the explaination. Works now!

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

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]))))