Solved

Dlookup and TEXT

Posted on 2014-01-01
7
592 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 34

Accepted Solution

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

863 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

26 Experts available now in Live!

Get 1:1 Help Now