CODEWITHSUNDEEP
vbams-access
barry17
barry17

Reputation: 153

Dlookup function in access?

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").

Upvotes: 0

Views: 242

Answers (1)

Andre
Andre

Reputation: 27634

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.

Upvotes: 1

Related Questions