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 Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")
"G Lam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> 秎ン
> news:(E-Mail Removed) い级糶...
> > 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 Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")
> >
> > "G Lam" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > 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
> >
> >
>
>
Similar Threads
