difference in if and iff handling a null value

I'm having a problem with handling null values in a vb6(sp6) program.
For some reason a if.else.endif statement seems to work fine with
detecting a null value and assigning a default if necessary(1). I
would like to use the iif statement instead(2), but it always throws
the error "Invalid use of Null" for some reason.

Anybody have any ideas why this is occurring?

Thanks in advance.

strIndex = "lngAppliedTo" & CStr(i)

(1)
If IsNull(recReceipt.Fields(strIndex)) Then
lngAppliedTo(i) = 0
Else
lngAppliedTo(i) = CLng(recReceipt.Fields(strIndex))
End If

(2)
lngAppliedTo(i) = IIf(IsNull(recReceipt.Fields(strIndex)), 0,
CLng(recReceipt.Fields(strIndex)))

wrote in message

| I'm having a problem with handling null values in a vb6(sp6) program.
| For some reason a if.else.endif statement seems to work fine with
| detecting a null value and assigning a default if necessary(1). I
| would like to use the iif statement instead(2), but it always throws
| the error "Invalid use of Null" for some reason.
|
| Anybody have any ideas why this is occurring?
|
| Thanks in advance.
|

| lngAppliedTo(i) = IIf(IsNull(recReceipt.Fields(strIndex)), 0,
| CLng(recReceipt.Fields(strIndex)))
|

Unfortunately, this is a known characteristic of the IIf function. Here
is the MSDN on it:

<quote>

IIf(expr, truepart, falsepart)

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.

</quote>

In your case, it will try to evaluate CLng(recReceipt.Fields(strIndex))
even if it is Null, causing the error.