Dlookup function in access?

0
votes

I'm trying to look up some values with my dlookup function in Access. I'm going into queries and pulling data with 2 different criteria. So I pull data from a query then I'm inserting it into a temp table. I'm having a hard time avoiding NULL values. 

 With rs2
    While Not .EOF
        lngVendorID = rs2("CompanyID")
        lngUnitPrice = rs2("UnitPrice")
        'Beginning Count
        lngBegCount = (DLookup("BegCount", "qryBegInv", "UnitPrice = " & [lngUnitPrice] & " AND CompanyID = " & [lngCompanyID] & ""))
        If IsNull(lngBegCount) Or lngBegCount = "" Then
            lngBegCount = 0
        End If
        .Edit
        rs2("BegInvCount") = lngBegCount
        .Update
        rs2.MoveNext
        Wend

I keep getting a variety of errors. Basically I want to see if DLOOKUP value is null, if it is, then use a 0 and insert that into the rs2("BegInvCount"), if it isn't null, then insert the lngBegCount into rs2("BegInvCount").

1
vbams-access
What are the errors and what are the circumstances? - nicomp

1 Answers

1
votes

Use the Nz() function to handle NULL values:

lngBegCount = Nz(DLookup("BegCount", "qryBegInv", _
    "UnitPrice = " & lngUnitPrice & " AND CompanyID = " & lngCompanyID & ""), 0)

You don't need brackets around variables.