Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DLookup using form text criteria

Posted on 2014-01-15
4
Medium Priority
?
609 Views
Last Modified: 2014-01-16
What is wrong with this:

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

Mnumber is a test field
0
Comment
Question by:SteveL13
  • 2
  • 2
4 Comments
 
LVL 58
ID: 39783771
Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = '" & me.cboMnumber & "'")

Jim.
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 39783949
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39785122
<<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
 
LVL 40

Expert Comment

by:PatHartman
ID: 39785531
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question