Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dlookup - 2 criteria, 1 needs be an and/or 1

Status
Not open for further replies.

cutestuff

Technical User
Sep 7, 2006
162
CA
hi,

I'm sure there is a simple answer out there somewhere.
I am trying to use the Dlookup function with 2 criteria. It works fine, but one of my criteria is optional (Me.txtItem).

I can only get it to work when the user selects BOTH criteria.

Here is my code:

Code:
tmpID = Nz(DLookup("id_punchlist", "tblPunchlistData", "system = '" & Me.cboSystem & "' and item_no = " & Me.txtItem), "0")

Thanks in advance.
 
You may try this:
tmpID = Nz(DLookup("id_punchlist", "tblPunchlistData", "system='" & Me.cboSystem & "' AND (item_no=" & Me.txtItem & " OR " & Nz(Me.txtItem, 0) & "=0)", "0")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
hi,

I tried it and it still doesn't work.

The error message I get is:
"Syntax error (missing operator) in query expression 'system = '3000-000' AND (item_no = OR 0)'. "

There must be some way to do this.
 
Could you please post YOUR actual code ?
If txtItem may be a ZLS or null, you may try this:
tmpID = Nz(DLookup("id_punchlist", "tblPunchlistData", "system='" & Me.cboSystem & "' AND (item_no=" & Me.txtItem & " OR " & Val(Me.txtItem & "") & "=0)", "0")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
hi PHV,

I figured it out.
Many thanks for the help though.
I ended up doing the following:

Code:
  'get the ID of the system and Item #
    If Not IsNull(txtItem) Then
       tmpCriteria = "system = '" & Me.cboSystem & "' and item_no = " & Me.txtItem & ""
    Else
        tmpCriteria = "system = '" & Me.cboSystem & "'"
    End If
    
    tmpID = Nz(DLookup("id_punchlist", "tblPunchlistData", tmpCriteria), "0")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top