Using numerous criteria with DLookup

Posted on 2009-02-23
Last Modified: 2012-05-06
Using the solution from :

I need to combine the search for Material, SPH and Cyl all from Table tStock.

I am able to pull out Material and SPH but when I combine then (and eventually Cyl) and am getting an error of invalid use of null:

iTest = DLookup("Qty", "tStock", "Material = '" & Me.LensMaterial & " AR" & "'" & " AND " & "[SPH] = " & FormatNumber(Me.ODSph, 2))

Any suggestions?
Question by:thandel
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    Author Comment

    This ultimately what I will need:  iTest = DLookup("Qty", "tStock", "Material = '" & Me.LensMaterial & " AR" & "'" & " AND " & "[SPH] = " & FormatNumber(Me.ODSph, 2) & " AND " & "[Cyl] = " & FormatNumber(Me.ODCyl, 2))

    Author Comment

    Just not sure why it is giving an error of "invalud use of null"

    Author Comment

    Ok I found the answer.... it was because the data on the form was not in the table.... any way to capture this "null" condition and put up a msgbox to the user?
    LVL 61

    Accepted Solution

    DLookup will return a null if no matching results are found.

    To handle this, decide what value you wnt returned if there are no matches found, and use the Nz function which returns either the value of the original expression or a value if that expression is null.

    Something = NZ(expression, ValueIfNull)

    Assuming you want to return zero if there are no records found by your DLookup:

    iTest =NZ( DLookup("Qty", "tStock", "Material = '" & Me.LensMaterial & " AR" & "'" & " AND " & "[SPH] = " & FormatNumber(Me.ODSph, 2)),0)

    LVL 61

    Assisted Solution

    Alternatively, to put up a message box as in your previous post, you can test for Null using IsNull.  Define varTest as Variant, which allows nulls:

    Dim varTest as Variant
    varTest = DLookup("Qty", "tStock", "Material = '" & Me.LensMaterial & " AR" & "'" & " AND " & "[SPH] = " & FormatNumber(Me.ODSph, 2))
    If isNull(varTest) then
        msgBox "the Dlookup returned no records"
        iTest = varTest  ' or simply do nothing
    End If

    Similarly to test the textbox for a value:

    if NZ(Me.ODSph,"") = "" then
       msgbox "Your textbox is empty"
      iTest =NZ( DLookup("Qty", "tStock", "Material = '" & Me.LensMaterial & " AR" & "'" & " AND " & "[SPH] = " & FormatNumber(Me.ODSph, 2)),0)
    end if

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now