Solved

Dlookup and TEXT

Posted on 2014-01-01
7
596 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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