IIF(IsNull)

[VR]

I built the expression: IIF(IsNull([Rate])," ",[AmtBilled]/[Rate])

I still get #Error. I want the field to either be left blank or a zero if
there is not a rate yet given or there will not be a rate. Can you please
help me?

 

[TedMi]

Nz([Rate], 0) will return Rate if not null, zero if null.

 

[David F Cox]

"IIf always evaluates both truepart and falsepart, even though it returns
only one of them. Because of this, you should watch for undesirable side
effects. For example, if evaluating falsepart results in a division by zero
error, an error occurs even if expr is True."

one way:

IIF(IsNull([Rate])," ",[AmtBilled]/Nz([Rate],1))

 

[Duane Hookom]

Your IIf() might return either a string or a number. I prefer to return a
single data type (or null) from any expression.
IIf(Nz([Rate],0)=0, 0, [AmtBilled]/[Rate])
or
IIf(Nz([Rate],0)=0, Null, [AmtBilled]/[Rate])