Invalid use of Null?

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

Alan Reinhart

I'm moving text data imported into a temp-table into other normalized
tables.

I have this (among other similar) command:

reqrs!cZXPartner = Val(raw!cZXPartner)

which took the text version and turned it into a LONG for the reqr table.
During the course of the import apparently a NULL value appeared in this
field and I got the error message: "Invalid Use of NULL".

First question is: Why didn't the VAL function just transmut the NULL into a
NULL?

So I tried this:

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

And it STILL wants to whine about an invalid NULL! I'm guessing it is
evaluating both sides of the IIf and getting upset over the NULL data being
in the VAL function.

What is the proper way to handle this?

=Alan R.
 
What value do you want to use for Null? zero? You can use the Nz function to
replace Null with your desired value:

reqrs!cZXPartner = Val(Nz(raw!cZXPartner, 0))

And yes, in an IIf in VBA code, both true and false parts of the IIf target
are evaluated. In an SQL query, however, they are not evaluated until the
IIf decides if the expression is true or false.
 
Back
Top