Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Using numerous criteria with DLookup

Posted on 2009-02-23
Medium Priority
Last Modified: 2012-05-06
Using the solution from : http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24170842.html

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
  • 3
  • 2
LVL 75
ID: 23718310

Author Comment

ID: 23718329
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

ID: 23718378
Just not sure why it is giving an error of "invalud use of null"
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 23718424
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

mbizup earned 2000 total points
ID: 23720656
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

mbizup earned 2000 total points
ID: 23720702
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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