Solved

DLookup using form text criteria

Posted on 2014-01-15
4
573 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 57
ID: 39783771
Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = '" & me.cboMnumber & "'")

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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 34

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now