Solved

Dlookup and TEXT

Posted on 2014-01-01
7
572 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

747 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

9 Experts available now in Live!

Get 1:1 Help Now