Multiple Criteria in DLOOKUP

[Guest]

Hello,
Using Access 2002. Keep getting a syntax error, Missing Operator.
I've tried 3 different versions, reviewed posts here, google searched and
found Ken Snell postings on mcse.ms archives and also reviewed microsoft
article id# 208786

Can someone please help me and tell me what is wrong with this DLOOKUP
statement?

dim temp_ID as string

temp_Status = "Available"

'*** VERSION 1
' temp_ID = DLookup("[ID]", "tbl_Plans", _
'"[DSPLY_CAT_NAME]= '" & Me.cbo_DSPLY_CAT_NAME & "'" & _
'" And [DSPLY_CAT_NAME_1]= " & Me.cbo_DSPLY_CAT_NAME_1 & _
'" And [DSPLY_CAT_NAME_2]= " & Me.cbo_DSPLY_CAT_NAME_2 & _
'" And [DSPLY_CAT_NAME_3]= " & Me.cbo_DSPLY_CAT_NAME_3 & _
'" And [PLAN_NAME]= " & Me.cbo_PLAN_NAME & _
'" And [P2K_SVC_NAME]= " & Me.cbo_PLAN_NAME2) & _
'" And [Status] = '" & temp_Status & "'")

'**** VERSION 2
temp_ID = DLookup("ID", "tbl_Plans", _
"DSPLY_CAT_NAME = """ & Me.cbo_DSPLY_CAT_NAME & """ AND
DSPLY_CAT_NAME_1 = " & _
Me.cbo_DSPLY_CAT_NAME_1 & " AND DSPLY_CAT_NAME_2 = " &
Me.cbo_DSPLY_CAT_NAME_2 & """ DSPLY_CAT_NAME_3 = " & _
Me.cbo_DSPLY_CAT_NAME_3)



'*** VERSION 3
'temp_ID = DLookup("[ID]", "tbl_Plans", _
'"[DSPLY_CAT_NAME]= '" & Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME] & "'" & _
'" And [DSPLY_CAT_NAME_1]= " & Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1]
& _
'" And [DSPLY_CAT_NAME_2]= " & Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2]
& _
'" And [DSPLY_CAT_NAME_3]= " & Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3]
& _
'" And [PLAN_NAME]= " & Forms![frm_Check_Out]![cbo_PLAN_NAME] & _
'" And [P2K_SVC_NAME]= " & Forms![frm_Check_Out]![cbo_PLAN_NAME2]) ' & _
'" And [Status] = '" & temp_Status"'")

MsgBox temp_ID

 

[Tom Lake]

Hello,
Using Access 2002. Keep getting a syntax error, Missing Operator.
I've tried 3 different versions, reviewed posts here, google searched and
found Ken Snell postings on mcse.ms archives and also reviewed microsoft
article id# 208786

Can someone please help me and tell me what is wrong with this DLOOKUP
statement?

temp_ID = DLookup("[ID]", "tbl_Plans", _
"[DSPLY_CAT_NAME]= '" & Me.cbo_DSPLY_CAT_NAME & "'" _
& " And [DSPLY_CAT_NAME_1]= '" & Me.cbo_DSPLY_CAT_NAME_1 & "'" _
& " And [DSPLY_CAT_NAME_2]= '" & Me.cbo_DSPLY_CAT_NAME_2 & "'" _
& " And [DSPLY_CAT_NAME_3]= '" & Me.cbo_DSPLY_CAT_NAME_3 & "'" _
& " And [PLAN_NAME]= '" & Me.cbo_PLAN_NAME & "'" _
& " And [P2K_SVC_NAME]= '" & Me.cbo_PLAN_NAME2 & "'"
& " And [Status] = '" & temp_Status & "'")

Tom Lake

 

[Allen Browne]

David, I suggest you use a String variable for the 3rd argument. You can
then print it, and see what is wrong. For example, the "missing operator"
message can occur when a control is null, and so the string has no number in
it and Access can't make sense of the string.

This suggestion is based on your Version 1:

Dim strWhere As String

strWhere = "([DSPLY_CAT_NAME]= '" & Me.cbo_DSPLY_CAT_NAME & _
"') And ([DSPLY_CAT_NAME_1]= " & Me.cbo_DSPLY_CAT_NAME_1 & _
") And ([DSPLY_CAT_NAME_2]= " & Me.cbo_DSPLY_CAT_NAME_2 & _
") And ([DSPLY_CAT_NAME_3]= " & Me.cbo_DSPLY_CAT_NAME_3 & _
") And ([PLAN_NAME]= " & Me.cbo_PLAN_NAME & _
") And ([P2K_SVC_NAME]= " & Me.cbo_PLAN_NAME2 & _
") And ([Status] = '" & temp_Status & "')"

Debug.Print strWhere
temp_ID = DLookup("[ID]", "tbl_Plans", strWhere)

Notes:
====
1. The brackets are optional.

2. Assumes the first combo is a Text field, but the others are Number (since
they don't have the quote delimiter.)

3. You need to test for Null before creating this string. Use IsNull().

4. The debug.print is just for debugging. Open the Immediate Window (Ctrl+G)
to see the results after running the code.

5. This won't work unless ALL conditions are met. That is, the
DSPLY_CAT_NAME field must match the value in cbo_DSPLY_CAT_NAME, and the
DSPLY_CAT_NAME_1 must also match the value in cbo_DSPLY_CAT_NAME_1, and ...
If they don't *all* match, you will get no result.

6. Where you have repeating fields names (such as DSPLY_CAT_NAME,
DSPLY_CAT_NAME_1, DSPLY_CAT_NAME_2, ...), it always means that you need a
related table. You need to redesign this so that you have a table of owners,
and a table of cats, so that one owner can have multiple cats.

7. The code will fail if the text field contains an apostropy, e.g. if the
cat's name is:
Fluffy's Revenge

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
Using Access 2002. Keep getting a syntax error, Missing Operator.
I've tried 3 different versions, reviewed posts here, google searched and
found Ken Snell postings on mcse.ms archives and also reviewed microsoft
article id# 208786

Can someone please help me and tell me what is wrong with this DLOOKUP
statement?

dim temp_ID as string

temp_Status = "Available"

'*** VERSION 1
' temp_ID = DLookup("[ID]", "tbl_Plans", _
'"[DSPLY_CAT_NAME]= '" & Me.cbo_DSPLY_CAT_NAME & "'" & _
'" And [DSPLY_CAT_NAME_1]= " & Me.cbo_DSPLY_CAT_NAME_1 & _
'" And [DSPLY_CAT_NAME_2]= " & Me.cbo_DSPLY_CAT_NAME_2 & _
'" And [DSPLY_CAT_NAME_3]= " & Me.cbo_DSPLY_CAT_NAME_3 & _
'" And [PLAN_NAME]= " & Me.cbo_PLAN_NAME & _
'" And [P2K_SVC_NAME]= " & Me.cbo_PLAN_NAME2) & _
'" And [Status] = '" & temp_Status & "'")

'**** VERSION 2
temp_ID = DLookup("ID", "tbl_Plans", _
"DSPLY_CAT_NAME = """ & Me.cbo_DSPLY_CAT_NAME & """ AND
DSPLY_CAT_NAME_1 = " & _
Me.cbo_DSPLY_CAT_NAME_1 & " AND DSPLY_CAT_NAME_2 = " &
Me.cbo_DSPLY_CAT_NAME_2 & """ DSPLY_CAT_NAME_3 = " & _
Me.cbo_DSPLY_CAT_NAME_3)



'*** VERSION 3
'temp_ID = DLookup("[ID]", "tbl_Plans", _
'"[DSPLY_CAT_NAME]= '" & Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME] & "'"
& _
'" And [DSPLY_CAT_NAME_1]= " &
Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1]
& _
'" And [DSPLY_CAT_NAME_2]= " &
Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2]
& _
'" And [DSPLY_CAT_NAME_3]= " &
Forms![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3]
& _
'" And [PLAN_NAME]= " & Forms![frm_Check_Out]![cbo_PLAN_NAME] & _
'" And [P2K_SVC_NAME]= " & Forms![frm_Check_Out]![cbo_PLAN_NAME2]) ' & _
'" And [Status] = '" & temp_Status"'")

MsgBox temp_ID