Invalid Use of Null

  • Thread starter Thread starter Anne
  • Start date Start date
A

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.
 
strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub]))

Why do I get the "invalid use of null" error message?
Click to expand...

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
 
Dev Ashish said:
strControlledSubstance = IIf(IsNull(varConSub), " and [CS]
like '*' ", " and [CS] = " & CStr([varConSub]))

Why do I get the "invalid use of null" error message?
Click to expand...

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)
Click to expand...

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().
 
Is there such a thing as vbEmptyString? I only know of vbNullString.
Click to expand...

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().
Click to expand...

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

Thanks for the corrections
-- Dev
 
Back
Top