How to do this dlookup?

  • Thread starter Thread starter G Lam
  • Start date Start date
G

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
 
I figured it out.
Dlookup("SlsOrdNbr", "TblOrd", "SlsOrdNbr =" &VarInFrmSlsOrd & "And BoxNbr =
"
& VarINFrmBoxNbr)
Fixed the problem
Gary
 
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
 
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.
Click to expand...
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
 
Allen,
Thank you. I will keep that in mind.
Gary
Allen Browne said:
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
Click to expand...
 
Back
Top