#Error in DLookup Function

Question

I am using a function in MS Access to return a Query field value with a DLookup. The function passes a value to strPersonType from an existing field in the query.

The function works fine when there is a value present in that field, however when there is no value present it returns #Error. In that case I would like it to return a Null or blank value.

I have tried error handling and If statements (like the one below) and I have been unable to fix it thus far.

No matter what I try the field value comes up as #Error.

Function LookUpPersonType2(strPersonType As String)            
    'Used to look up Person Type2 in the "Person Type Key" table given PERSON_TYPE
    'Returns original supplied name if no variation


    If IsNull(strPersonType) Or strPersonType = "" Then
        LookUpPersonType2 = ""
    Else

        If IsNull(DLookup("[Person Type2]", "[Person Type Key]", "[PERSON_TYPE] ='" & strPersonType & "'")) Then
            LookUpPersonType2 = strPersonType
        Else
            LookUpPersonType2 = DLookup("[Person Type2]", "[Person Type Key]", "[PERSON_TYPE] ='" & strPersonType & "'")
        End If
    End If
    Debug.Print LookUpPersonType2 & ","
End Function

Have you tried the NZ function? As LookUpPersonType2=NZ(DLOOKUP(...) , NULL) — Darren Bartrup-Cook

Chuck0185 Chuck0185 · Accepted Answer · 2017-11-06T18:38:39

I realized that I needed to change the passed parameter to a Variant variable type since there is a possibility that the value will be NULL. Originally it was string variable type and strings cannot be NULL. Thanks!

#Error in DLookup Function

1 Answers