Invalid Use of Null

[Anne]

strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub]))

Why do I get the "invalid use of null" error message?
This is an integer data type. I tried first converting it
to variant and then evaluating but this is not working.
Also doesn't work if you don't convert it.

Appreciate any help you can give - thanks.

 

[Dev Ashish]

strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub]))

Why do I get the "invalid use of null" error message?

IIF evaluates both TRUE and FALSE conditions. VBA/VB do not have any short-
circuiting like in Java/C++, so no matter what's the value of varConSub,
CStr([VarConSub]) will always execute before strControlledSubstance gets
the value you expect. This is most likely where the problem occurs. To
work around it, use

strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub] & vbEmptyString))

The concatenation with an empty string will force a string to be sent to
CStr and the statement will work. Try these 2 lines in debug window.

?CStr(Null & vbEmptyString)
?Cstr(Null)

-- Dev

 

[Dirk Goldgar]

strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub]))

Why do I get the "invalid use of null" error message?

IIF evaluates both TRUE and FALSE conditions. VBA/VB do not have any
short- circuiting like in Java/C++, so no matter what's the value of
varConSub, CStr([VarConSub]) will always execute before
strControlledSubstance gets the value you expect. This is most
likely where the problem occurs. To work around it, use

strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub] & vbEmptyString))

The concatenation with an empty string will force a string to be sent
to CStr and the statement will work. Try these 2 lines in debug
window.

?CStr(Null & vbEmptyString)
?Cstr(Null)

Dev -

Is there such a thing as vbEmptyString? I only know of vbNullString.
It seems to me also that if you were to force [varConSub] to a string by
concatenating it with vbNullString (vbEmptyString), there'd no longer be
any reason to surround the result with CStr().

 

[Dev Ashish]

Is there such a thing as vbEmptyString? I only know of vbNullString.

Sorry, my typo. It should be vbNullString.

It seems to me also that if you were to force [varConSub] to a string by
concatenating it with vbNullString (vbEmptyString), there'd no longer be
any reason to surround the result with CStr().

Yup.. I copied/pasted from original post without fixing it up.

Thanks for the corrections
-- Dev