DLookup return value

[faythe1215]

I am currently running a script that uses DLookup. If the item is found, DLookup will return a number (used as currency) and calculations will be performed on this item. As I understand it, if DLookup does not return a value, it will return as NULL. Is there a way to check if it returns NULL?



Originally, my code looked like this, but it returned with a mismatch type error.

    Dim Price As Currency

    Price = DLookup("[CostUnit]", "qryAvgIngredientCost", "[Item_ID] = " & CurrentItem)
    If Price Is Null Then
        MsgBox ("Item not found. Cost calculation will be incomplete.")
        Else
            ' Calculations
    End If

Here, I tried with a variant type to handle "Is Null" and it returned with a 424 error.

    Dim Price As Currency
    Dim LookTest as Variant

    LookTest = DLookup("[CostUnit]", "qryAvgIngredientCost", "[Item_ID] = " & CurrentItem)
    If LookTest Is Null Then
        MsgBox ("Item not found. Cost calculation will be incomplete.")
        Else
            Price = LookTest
            ' Calculations
    End If

[orange]

You can use the NZ() function. If a Null is returned, you can have it default to any value you want eg 0.

for example
Dim looktest as currency

LookTest = NZ(DLookup("[CostUnit]", "qryAvgIngredientCost", "[Item_ID] = " & CurrentItem),0)

[ssanfu]

Currency type fields cannot handle Nulls. I think that only Variants can contain nulls. One way to deal with Nulls is to use the NZ() function (null to zero). In VBA, you should use the IsNull() function to check for Nulls.

    Dim Price As Currency

    Price = Nz(DLookup("[CostUnit]", "qryAvgIngredientCost", "[Item_ID] = " & CurrentItem), 0)
    '    If IsNull(Price) Then
    If Price = 0 Then
        MsgBox ("Item not found. Cost calculation will be incomplete.")
    Else
        ' Calculations
    End If

or

    Dim Price As Currency
    Dim LookTest As Variant

    LookTest = DLookup("[CostUnit]", "qryAvgIngredientCost", "[Item_ID] = " & CurrentItem)
    If IsNull(LookTest) Then
        MsgBox ("Item not found. Cost calculation will be incomplete.")
    Else
        Price = LookTest
        ' Calculations
    End If

End Sub

[faythe1215]

Perfect! Thanks!