Solved

DLookup using form text criteria

Posted on 2014-01-15
4
595 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 58
ID: 39783771
Me.txtTotSqFt = Me.txtqty * DLookup("SizeSqft", "tblCatalog", "Mnumber = '" & me.cboMnumber & "'")

Jim.
0
 
LVL 38

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 58

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 38

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

617 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