Access DLookup RunTime 94 error - Invalid use of Null

Question

I want to obtain the lower_limit value searching from the table Threshold where it is stored but I'm always getting: "Error RunTime 94: Invalid use of Null value". I don't want the subroutine to give me back lower_limit, its only useful for the further step of SearchAbnormalities. I have checked the table Threshold (which PK is composed by ID_parInput, rangeagein, gender) and there is the row which corresponds to this selection.

Private Sub SearchAbnormalities(ByVal ID_parInput As Integer, ByVal gender As String, ByVal eta As Integer, Optional ByVal insertedSysValue As Integer, Optional ByVal insertedDiaValue As Integer, Optional ByVal measureValue As String)

Dim rangeagein As String
Dim lower_limit As Integer
Dim upper_limit As Integer
    
rangeagein = giveMeTheRange(eta)
Debug.Print ID_parInput, gender, rangeagein '>>   12, female, middle(31-60)
    
lower_limit = DLookup("lower_limit", "Threshold", "rangeage ='" & rangeagein & "' And ID_par = " & ID_parInput & " And gender = '" & gender & "'")
    
'...

End Sub

Answer 1

This is not the correct way to handle the DLookup().

The DLookup() function returns Null when the lookup value is not found and regular value type variables (string, integer, date etc.) cannot accept null values. Trying to assign null, will generate the famous runtime error 94 - Invalid use of Null.

Therefore, you need to assign the return value to a Variant, which accepts nulls. Then you can check if a value returned and act accordingly.

Dim returnValue As Variant, lower_limit As Integer

returnValue = DLookup("lower_limit", "Threshold", "rangeage ='" & rangeagein & "' And ID_par = " & ID_parInput & " And gender = '" & gender & "'")

'check for null
If IsNull(returnValue) Then
    'action for not found value
    'you may as well exit here
End If

'found, safe to assign
lower_limit = returnValue 

Another approach is to wrap the DLookup with the Nz() built-in function, which basically does pretty much the same. If the value is not found, return the second argument.

Dim value As String
value = Nz(DLookup("a","b","c=0"), "Not found")

Regarding the returned null value, the DLookup looks fine to me. As a matter of fact, printing the where condition generates this:

'rangeage ='middle(31-60)' And ID_par = 12 And gender = 'female' 

Since it returns null, you better double-check your data.