[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Dlookup and TEXT

Posted on 2014-01-01
7
Medium Priority
?
616 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
  • 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 49

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 49

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 41

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Implementing simple internal controls in the Microsoft Access application.
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 …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

611 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