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

[cutestuff]

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:

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

Thanks in advance.

 

[PHV]

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

 

[cutestuff]

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.

 

[PHV]

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

 

[cutestuff]

hi PHV,

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

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