DLookup using form text criteria

What is wrong with this:

Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = " & "'"me.cboMnumber"'"&"))

Mnumber is a test field
SteveL13Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Jim's solution is to use single quotes which is certainly easier to visualize and works fine as long as Mnumber never includes a single quote. >>

On that point, I think a slightly better way is this:

Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = " & chr$(34) &  me.cboMnumber & chr$(34))

 Chr$(34) giving you a quote character.

 The same problem still exists however; if there is a quote (") in Me.cboMnumber, your going to get an error.

 Using chr$(34) however lets you visualize the statement better, as often a single/double quote pair can be difficult to figure out depending on the font and point size.  ie.

"'

vs

'"

or

''"

which is two singles, followed by a double.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = '" & me.cboMnumber & "'")

Jim.
0
 
PatHartmanCommented:
It would be helpful if you gave us a clue what is wrong.  Are you getting an error message? is the calculation incorrect?

You have an extra closing paren in addition to incorrect usage of double quotes.  Jim's solution is to use single quotes which is certainly easier to visualize and works fine as long as Mnumber never includes a single quote.
0
 
PatHartmanCommented:
Personally, I always create a constant in my app named QUOTE and I use it whenever I need to embed double quotes in a string.  So that would make the expression:

Public Const QUOTE = """"



Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = " & QUOTE &  me.cboMnumber & QUOTE)
 

Open in new window


The constant declaration needs to go in a standard module.  I create one that I use exclusively for global variables so I don't need to hunt around for them.

I also build the criteria into a variable so it is easy to examine should there be an issue.
Public Const QUOTE = """"


Dim sCriteria AS String
sCriteria = "Mnumber = " & QUOTE &  me.cboMnumber & QUOTE
Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", sCriteria)
 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.