Getting past a NULL

  • Thread starter Thread starter Alan Reinhart
  • Start date Start date
A

Alan Reinhart

(This is a re-phrasing if a previous question)

In VB Access 2003 it appears doing a direct assignment of a NULL is a No-No.

For testing purposes:

Dim x as integer
x = NULL

....produces the error message "Invalid use of NULL" (Error 94)

Likewise,

reqrs!cZXPartner = IIf(IsNull(Val(raw!cZXPartner)), 0, Val(raw!cZXPartner))

produces the same error.

If a NULL is detected in the imported raw data I want to use zero as the
value to be entered in its place in the new table.

How can this be done?
(I'm thinking I may have to resort to a full IF-ELSE block but this seems a
bit clumsy. Especially if I had a lot of incoming text fields and had to
validate EVERY one.)

=Alan R.
 
The only data type that can have a value of Null is Variant.

In the 2nd case, you don't want the Val in the IsNull function:

reqrs!cZXPartner = IIf(IsNull(raw!cZXPartner), 0, Val(raw!cZXPartner))
 
reqrs!cZXPartner = IIf(IsNull(Val(raw!cZXPartner)), 0,
Click to expand...
Val(raw!cZXPartner))

Addendeum/Correction to above:

reqrs!cZXPartner = IIf(IsNull(raw!cZXPartner), 0, Val(raw!cZXPartner))

should be the correct statement. The main test if the IIf should
operate/test on the specific column, and not use the VAL function. Bug
fixed... HOWEVER (!!) the @#&@*# statement still produces the dreaded
"Invalid use of NULL" error!

A simple test like this should NOT be (sic) rocket-surgery!!!! What gives
here??

=Alan R.

PS - still can't quite understand why I can't do a direct assignement of a
NULL. It is my code and I should be able to put NULL's wherever I want to...
but I digress.
 
You're getting hit by the fact that the IIf statement doesn't "short
circuit". From the Help file:

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.

In other words, get rid of the Val, or else use

reqrs!cZXPartner = Val(IIf(IsNull(raw!cZXPartner), 0, raw!cZXPartner))

Sorry that I missed this the first time around: IIf is actually inconsistent
in that it WILL short circuit in queries, but not in VBA, and I tend to
forget which is which.
 
Back
Top