How to do this dlookup?

[G Lam]

Hi, I have a little problem in a Dlookup statement.
If I wrote:
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr = " & VarInFrmSlsOrd) It works
If I Wrote:
Dlookup("SlsOrdNbr", "TblOrd", "BoxNbr = " & VarInFrmBoxNbr) - It works
If I Wrote:
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr = SO1081 And BoxNbr = 3")
The SlsOrdNbr and BoxNbr are hard coded, but works.
If I wrote:
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr =" &VarInFrmSlsOrd And "BoxNbr = "
& VarINFrmBoxNbr)
I got Type Mismatch error.
How can I do this right?
Thank you.
Gary

 

[G Lam]

I figured it out.
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr =" &VarInFrmSlsOrd & "And BoxNbr =
"
& VarINFrmBoxNbr)
Fixed the problem
Gary

 

[Allen Browne]

From your example, it looks as if SlsOrdNbr is a Text type field. You
therefore need quote marks as delimiters around the value for that field.
The And also needs to go inside the quotes.

Try:
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr = """ &
VarInFrmSlsOrd & """ And BoxNbr = " & VarINFrmBoxNbr)

More information on how to build the 3rd argument for DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[G Lam]

Allen,
Thank you.
Yes, the SlsOrdNbr field is a String field, while the BoxNbr field is a
Integer field.
I always wonder how and why these "s are added. I saw some examples, like
the one you did, had two or three " in a row. IN my case,
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr = """ &
VarInFrmSlsOrd & """ And BoxNbr = " & VarINFrmBoxNbr) -> won't work.
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr = " &

VarInFrmSlsOrd & " And BoxNbr = " & VarINFrmBoxNbr) -> Worked.

Gary

 

[Allen Browne]

Text fields need the quote as a delimiter.

The string you need for the 3rd argument needs to contain quotes.
To indicate this is not the end of the string, embedded quotes must be
doubled in VBA, i.e. to get a string containing:
Surname = "O'Brien"
you must use:
"Surname = ""O'Brien"""
If the name were in a text box, you would need:
"Surname = """ & MyTextBox & """"
That's:
open quote
field name
equals
quote mark in the string must be doubled
closing quote
concetanate the value from the text box.
open quote
quote mark in the string must be doubled
closing quote

 

[G Lam]

Allen,
Thank you. I will keep that in mind.
Gary

Text fields need the quote as a delimiter.

The string you need for the 3rd argument needs to contain quotes.
To indicate this is not the end of the string, embedded quotes must be
doubled in VBA, i.e. to get a string containing:
Surname = "O'Brien"
you must use:
"Surname = ""O'Brien"""
If the name were in a text box, you would need:
"Surname = """ & MyTextBox & """"
That's:
open quote
field name
equals
quote mark in the string must be doubled
closing quote
concetanate the value from the text box.
open quote
quote mark in the string must be doubled
closing quote