Invalid use of Null?

[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.

 

[Ken Snell]

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.

 

[John Vinson]

What is the proper way to handle this?

You're right, Val() ought to be smarter!

I'd suggest

IIF(IsNull(raw!cZXPartner), NULL, Val(NZ(raw!cZXPartner)))