Solved

DLookup using form text criteria

Posted on 2014-01-15
4
578 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

919 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

15 Experts available now in Live!

Get 1:1 Help Now