Solved Invalid Use of Null Error

[dgreen]

System_Acronym value is populated
System_Name is not populated

I'm trying to get the CaptureName code to run and while it knows that Me.System_Name is null, it's erroring on this line.
The code needs to handle situations where the system name is null.
The code compiles.

CaptureName = StrConv(Trim(ReplaceIllegalChars(Me.System_Acronym)), vbUpperCase) & IIf(IsNull(Me.System_Name), " ", " (" & StrConv(Trim(ReplaceIllegalChars(Me.System_Name)), vbProperCase) & ")")

 

[cheekybuddha]

IIRC, IIf() evaluates both the True and False part of the expression, even if the first part evaluates to True.

Try:

CaptureName = StrConv(Trim(ReplaceIllegalChars(Me.System_Acronym)), vbUpperCase) & IIf(IsNull(Me.System_Name), " ", " (" & StrConv(Trim(ReplaceIllegalChars(Me.System_Name & "")), vbProperCase) & ")")

hth,

d

 

[CJ_London]

suggest try

CaptureName = StrConv(Trim(ReplaceIllegalChars(Me.System_Acronym)), vbUpperCase) &  " (" & StrConv(Trim(ReplaceIllegalChars(nz(Me.System_Name,"")), vbProperCase) & ")"

 

[June7]

IIf() in Access (as in textbox ControlSource) and VBA will evaluate both parts regardless of which is returned by the conditional.
IIf() in SQL statement only evaluates the one that is returned by conditional.

 

[dgreen]

Thanks team. I ended up using @cheekybuddha solution.

 

[cheekybuddha]

Thanks, @June7, for the clarification. It's this kind of inconsistent behaviour that is often irritating in Access. The inability to 'short-circuit' in VBA really ought to classed as a bug!

@dgreen, I'm pleased it worked for you. I had first considered the same expression as @CJ_London suggested, but I guessed you didn't want empty trailing parentheses if Me.System_Name was Null.