#error when running query

[AHJ1]

I'm running a query that calls a function.

SELECT MyDataAggregated.ID, PutAppealsToNotes1([Appeal_Not_Heard_1],[Appeal_Not_Heard_2],[Exemption_Description]) AS Notes1, PutAppealsToNotes1([Appeal_Not_Heard_1],[Appeal_Not_Heard_2]) AS Notes2, [Notes1] & [Notes2] AS Notes
FROM MyDataAggregated;

Function PutAppealsToNotes1(Optional strCBEAppealString1 As String, Optional strCBEAppealString2 As String, _
Optional strCBE_f_ExemptDescr As String)
'->INCOMPLETE LOGIC

Dim strContent As String
 strContent = strCBEAppealString1 & vbCrLf & strCBEAppealString2 & vbCrLf
strContent = "Developing code - " & strContent
If Len(strCBE_f_ExemptDescr) > 0 Then
    strContent = strContent & strCBE_f_ExemptDescr
End If

    PutAppealsToNotes1 = strContent

End Function

The problem is that if there is no data in the field [Exemption_Description] the query returns #Error in that cell of the query.

What's the best way to preclude this result? The IsNull and IsMissing functions return false.

 

[lameid]

Change your input variables from strings to variants and within the function test for nulls (isnull function).

 

[AHJ1]

Yes. That works. Thank you. I realize that a zero-length string is not the same as a Null. Do you happen to have the code for checking for zero-length strings?

 

[PHV]

To check either Null or ZLS:
If Trim(testedVar & "") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AHJ1]

Simple. Thank you.