Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dlookup and TEXT

Posted on 2014-01-01
7
Medium Priority
?
608 Views
Last Modified: 2014-01-01
Experts, guess I still dont get the order of the single quotes and double quotes.  I get a #error on the below.  I have spent an hour on this.  Both [LCNo] and [Reference Number] are TEXT.  

Do you see where I have made an error?  

unbound field on form (names are correct):
=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= & chr(34) &" & [Reference Number] & " & Chr(34) &")

thank you
0
Comment
Question by:pdvsa
[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
  • 2
  • +1
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39750175
Try this:

=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= " & chr(34) & LCNoTextbox & CHR(34) & " AND [Reference Number] = " & Chr(34)  & ReferenceNumberTextbox & CHR(34) )

Open in new window


Your quotes were not quite right, and you need to provide some values through form controls.
0
 

Author Comment

by:pdvsa
ID: 39750196
Hi Mbizup, thanks for the response.  

I dont completely follow why I need an AND?  Do I change LCNoTextbox  to a control name on my form or leave as is?

Here is computer lingo for what I am trying to do:
I am looking up tblLetterOfCredit.OldLCNo where me.Reference Number = tblLetterOfCredit.OldLCNo

Let me know if that changes something.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39750278
pdvsa,

I have a function (fnWrap) that I use to wrap things (text and dates mostly).

it looks like:
Public Function fnWrap(WrapWhat As Variant, _
                      Optional WrapWith As String = """") As Variant

    'Wraps a string or text value in a character, default is the double quote
    If IsNull(WrapWhat) Then
        fnWrap = WrapWhat
    Else
        fnWrap = Replace(WrapWhat, WrapWith, WrapWith & WrapWith)
    End If
    
End Function

Open in new window

With this function, you can write your DLOOKUP as:

=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= " & fnWrap(LCNoTextbox) & " AND [Reference Number] = " & fnWrap(ReferenceNumberTextbox))

This way, you don't have to worry about adding additional chr$(34) or chr$(39) - single quotes, or even # for dates

To do a date, you might do:

=DLOOKUP("SomeField", "YourTable", "[DateField] = " & fnWrap(me.txtDateControl, "#"))

I tend to be lazy.  This method is shorter than almost all of the other methods, is far easier to read than the methods that try to embedd multiple single or double quotes inside other quotes, and even takes care of the case where the text you are trying to wrap in quotes contains a " character.

This is one of my most used functions in my applications.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39750343
BTW,

The best way to debug that would be to put some code in the AfterUpdate event of those two textboxes, something like:
Dim strCriteria as string

strCriteria = "[LCNo]= " & fnWrap(NZ(me.LCNoTextbox, "")) & " AND " _
         & "[Reference Number] = " & fnWrap(NZ(me.ReferenceNumberTextbox, ""))
debug.priont strCriteria

Open in new window

In reality, you might actually want to use something like the following as your DLOOKUP, since one or both of those fields could be empty (NULL):
=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= " & fnWrap(NZ(me.LCNoTextbox, "")) & " AND [Reference Number] = " & fnWrap(NZ(me.ReferenceNumberTextbox, "")))

Open in new window

0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1200 total points
ID: 39750374
I dont completely follow why I need an AND? 

Open in new window

 Don't confuse the concatenation operator - & with the logical operator - AND.  The way you wrote the expression is confusing and looks like you are trying to create a compound condition which is why mbizup offered the suggestion she did.

It will help you if you step back and think about the condition and try to separate that in your mind from the task of building a string to represent it.

It looks like you want to end up with
beginstring---[LCNo] = "sometextvalue" AND  [Reference Number] = "sometextvalue"--endstring
Although, it could be that [Reference Number] is a control or column that will contain the text value you are looking for.  And syntactically, it will make a difference.  In that case, you want to end up with
beginstring---[LCNo] = "sometextvalue"---endstring --   as the actual string you are trying to create.

It is hard to get your head around embedding quotes in strings and why it is necessary (you have a string within a string).  If your embedded string will not include single quotes, the syntax is simpler to read if you use the single quote as the delimiter for the embedded text string.  So, you might need something like:
=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= '" & [Reference Number] & "'")
Examine the simplified string so you can see how the single quote works.  If you need to use the double quote, then
=DLookUp("[OldLCNo]","tblLetterOfCredit","[LCNo]= & chr(34) & [Reference Number] & Chr(34))
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39750416
<< I dont completely follow why I need an AND? >>

<<
I am looking up tblLetterOfCredit.OldLCNo where me.Reference Number = tblLetterOfCredit.OldLCNo
>>

Based on your clarified question, you don't need the AND.

The expressions at the end of Pat's comment should do the trick.

Jim Dettman has a very clear article on this topic:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
0
 

Author Closing Comment

by:pdvsa
ID: 39750615
thank you.  

fyed:  It is true I do have that WRAP function and I completely forgot about it.  I am sure you read the other posts about me not needing the AND.  thank you sir...

thx Miriam.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

664 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