DLookUp with 2 criteria problem

[noe1818 via AccessMonster.com]

Here is what's going on. I have two tables
1- table checkmark sub
2- table checkmarks sub2
I keep getting an "error?" message when I run my procedure.
I want an unbound text box on a form to lookup if a checkbox was chosen then
display "yes" or "no".
I'm trying to do it using this:
IIf (DLookUp("[checkmark]","table checkmark sub",'[id]=' & [id] & " And [ass
#]= " & [ass #])=True, "yes","No").
Where checkmark, id, ass # are the field names of "table checkmark sub". "id"
and "ass #" are the same in both tables
In other words, if a lookup of the checkmark value is true, then indicate
"yes", otherwise indicate "no".
Now my checkmark value is a yes/no type, does that matter? Do I have the
syntax right? Can someone help?

 

[Stefan Hoffmann]

hi,

IIf (DLookUp("[checkmark]","table checkmark sub",'[id]=' & [id] & " And [ass
#]= " & [ass #])=True, "yes","No").
Now my checkmark value is a yes/no type, does that matter? Do I have the
syntax right? Can someone help?

First place the table name into square brackets: "[table checkmark sub]"
Then correct the criteria. It is a string and needs the normal string
delimiters "":

IIf(

DLookUp("[checkmark]",
"[table checkmark sub]",
"[id]=" & [id] & " And [ass #]= " & [ass #])

, "Yes", "No"
)


mfG
--> stefan <--

 

[noe1818 via AccessMonster.com]

I did some trial and error and got it to work using the followin:
=IIf(DLookUp("[checkmark]","[table checkmark sub]","[id]=""" & [id] & """"
And [ass #]=""" & [ass #]")=True,"yes","no")

The problem is that every result comes back "no" when there should be a
couple of "yes"(s).
[id]'s are text but have numbers in them, and [a.s] (as you put it) is number
type.

hi,

IIf (DLookUp("[checkmark]","table checkmark sub",'[id]=' & [id] & " And [ass
#]= " & [ass #])=True, "yes","No").
Now my checkmark value is a yes/no type, does that matter? Do I have the
syntax right? Can someone help?

First place the table name into square brackets: "[table checkmark sub]"
Then correct the criteria. It is a string and needs the normal string
delimiters "":

IIf(

DLookUp("[checkmark]",
"[table checkmark sub]",
"[id]=" & [id] & " And [ass #]= " & [ass #])

, "Yes", "No"
)

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I did some trial and error and got it to work using the followin:
=IIf(DLookUp("[checkmark]","[table checkmark sub]","[id]=""" & [id] & """"
And [ass #]=""" & [ass #]")=True,"yes","no")
The problem is that every result comes back "no" when there should be a
couple of "yes"(s).
[id]'s are text but have numbers in them, and [a.s] (as you put it) is number
type.

Try this:

IIf(

DLookUp("[checkmark]",
"[table checkmark sub]",
"[id]='" & [id] & "' And [ass #]= '" & [ass #] & "'")

, "Yes", "No"
)


mfG
--> stefan <--

 

[J_Goddard via AccessMonster.com]

Hi -

If [Ass #] is numeric, it it should not have quote marks around the value:

Try:

IIf(DLookUp("[checkmark]","[table checkmark sub]",

"[id]= """ & [id] & """ And [ass #]=" & [ass #])=True,"yes","no")

John

I did some trial and error and got it to work using the followin:
=IIf(DLookUp("[checkmark]","[table checkmark sub]","[id]=""" & [id] & """"
And [ass #]=""" & [ass #]")=True,"yes","no")

The problem is that every result comes back "no" when there should be a
couple of "yes"(s).
[id]'s are text but have numbers in them, and [a.s] (as you put it) is number
type.

hi,

[quoted text clipped - 17 lines]

mfG
--> stefan <--

 

[noe1818 via AccessMonster.com]

It worked, it worked, it worked!!!!!! Thanks, now I can continue on with my
life. Thanks a mill John
-Noe

Hi -

If [Ass #] is numeric, it it should not have quote marks around the value:

Try:

IIf(DLookUp("[checkmark]","[table checkmark sub]",

"[id]= """ & [id] & """ And [ass #]=" & [ass #])=True,"yes","no")

John

I did some trial and error and got it to work using the followin:
=IIf(DLookUp("[checkmark]","[table checkmark sub]","[id]=""" & [id] & """"

[quoted text clipped - 10 lines]

 

[noe1818 via AccessMonster.com]

Oh by the way, how many " would I use if one of my criteria were date types?
ex. And [date table1]= & [date table2]

It worked, it worked, it worked!!!!!! Thanks, now I can continue on with my
life. Thanks a mill John
-Noe

Hi -

[quoted text clipped - 13 lines]

 

[J_Goddard via AccessMonster.com]

Dates use # to delimit the values instead of single or double quotes:

"..And [date table1]= #" & [date table2] & "#"

A note of caution: from what I understand from these newsgroups, Access only
understands the American mm-dd-yyyy (yuk!) format in this situation, so

"..And [date table1]= #" & format([date table2],"mm-dd-yyyy") & "#"

would be safer.

John

Oh by the way, how many " would I use if one of my criteria were date types?
ex. And [date table1]= & [date table2]

It worked, it worked, it worked!!!!!! Thanks, now I can continue on with my
life. Thanks a mill John

[quoted text clipped - 5 lines]

 

[Stefan Hoffmann]

hi John,

"..And [date table1]= #" & [date table2] & "#"
A note of caution: from what I understand from these newsgroups, Access only
understands the American mm-dd-yyyy (yuk!) format in this situation, so
"..And [date table1]= #" & format([date table2],"mm-dd-yyyy") & "#"
would be safer.

This is correct. Using three simple functions makes live a lot easier here:

Public Function SQLDate(ADateTime As Date) As String

SQLDate = "#" & Format([date table2], "mm-dd-yyyy") & "#"

End Function

Public Function SQLEscape(AString As String) As String

SQLEscape = Replace(AString, "'", "''")

End Function

Public Function SQLString(AString As String) As String

SQLString = "'" & SQLEscape(AString) & "'"

End Function


E.g.

Dim strSQL As String

strSQL = "WHERE [Name] = " & SQLString(txtInput.Value) & _
"AND [Date] = " & SQLDate(txtDate.Value)


mfG
--> stefan <--