IIF(IsNull)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VR - 09 Oct 2006 14:50 GMT

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?

Reply to this Message

TedMi - 09 Oct 2006 15:43 GMT

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

Signature

Ted

> 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?

Reply to this Message

David F Cox - 09 Oct 2006 15:47 GMT

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

>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?

Reply to this Message

Duane Hookom - 09 Oct 2006 15:58 GMT

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

Signature

Duane Hookom
MS Access MVP

>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?

Reply to this Message