IsNull and IIF and error ;)

[Krzysztof Bartosiewicz]

Hi!
I have a problem with IIF and IsNull and it annoys me very much.. Maybe you
will now why Access behaves in such a way..

The problem is that if I use sth like that

If IsNull(Sth) Then
Sth_else = Chr(34) & " " & Chr(34)
Else
Sth_else = SomeFunction(Sth)
End If

everything is ok. However if I try the same thing but in another manner:
sth_else = IIf(IsNull(sth), Chr(34) & " " & Chr(34), somefunction(sth))

Access throws an error about invalid use of null..
Any suggestions? Am I right and that is impossible in access or maybe I do
something wrong?

Thanks in advance for help
Chris

 

[Rick Brandt]

Hi!
I have a problem with IIF and IsNull and it annoys me very much.. Maybe you
will now why Access behaves in such a way..

The problem is that if I use sth like that

If IsNull(Sth) Then
Sth_else = Chr(34) & " " & Chr(34)
Else
Sth_else = SomeFunction(Sth)
End If

everything is ok. However if I try the same thing but in another manner:
sth_else = IIf(IsNull(sth), Chr(34) & " " & Chr(34), somefunction(sth))

Access throws an error about invalid use of null..
Any suggestions? Am I right and that is impossible in access or maybe I do
something wrong?

In the If-Then block the "Else" code only runs when Sth is not null. Both
return expressions of an IIf() are evaluated even though only one value is
returned. Your SomeFunction() obviously doesn't except a Null as its argument,
thus the error.

The help topic for IIf() explicitly warns about this.