?
Solved

Dlookup Won't Return value

Posted on 2009-02-08
25
Medium Priority
?
463 Views
Last Modified: 2013-11-25
I am tryuing to get email addresses out of a table based on branch number using the DLOOKUP.  But every time runs, I get the "You canncelled the operation" .  This usually comes about if my quotes  are not correct.  The valure for strMaxBranch is a number but in the talbe it is a text.  I thought it was automatically converting to a number, but I still get the same error.  I simply cannot see the problem.
strTo = DLookup("EmailAddressTo", "tblEmailMessages", "BR_NBR = '" & strMaxBranch & "' ")
 
 
?DLookup("EmailAddressTo", "tblEmailMessages", "BR_NBR = 79 ")

Open in new window

0
Comment
Question by:ssmith94015
  • 10
  • 6
  • 5
  • +1
25 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 23584176
Well in your code you have single quotes around strMaxBranch and in your Immediate Window test you didn't use single quotes:

?DLookup("EmailAddressTo", "tblEmailMessages", "BR_NBR = 79 ")

to match would be

?DLookup("EmailAddressTo", "tblEmailMessages", "BR_NBR = '79' ")

What line of code exactly is your program failing at.  The dlookup looks ok but of course it will return NULL if the lookup fails so you need to be checking for that.
0
 

Author Comment

by:ssmith94015
ID: 23584190
I tried both versions, was showing a sample of what I tried.  Neither worked.  It fails at this DLOOKUP.  I was trying to see it if failed with a hard-coded value and still does.  But the value needs to be dynamic.  That is, passed in as the strMaxBranch variable.
0
 

Accepted Solution

by:
ssmith94015 earned 0 total points
ID: 23584350
I finally got a work-around functioning.  I still do not know why the dybamic version does not work, but I need to get to other issues.  Thank you for at least taking a look, but there is no need for a solution at present now.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ssmith94015
ID: 23584359
I am requesting to simply close this question.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 23584764
What was your workaround?
0
 
LVL 75
ID: 23584877
This should work ... IF ... strMaxBranch is a String value (?)

strTo = DLookup("[EmailAddressTo]", "[tblEmailMessages]", "[BR_NBR] = " & Chr(34)  & strMaxBranch & Chr(34) )
 
 
0
 
LVL 75
ID: 23584879
Note:

Using single quotes in Access criteria strings (WHERE Clauses) is problematic.

mx
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 23585047
I'm not aware of single quotes being problematic in where clauses any more than double quotes are (in the case where the string you're quoting has single or double quotes inside it) but in the case where the string is going to be numeric digits there shouldn't be any problem at all should there?
0
 
LVL 75
ID: 23585072
The problem comes when the text string itself (eg strMaxBranch ) contains single quotes, for example

O'Reilly

mx
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 23585897
Right, so the same thing applies if it has double quotes inside it and you used double quotes to quote thes tring.  You need to properly escape the string you're using by replacing any single quotes in the string (if you use single quotes for quoting) with two single quotes in a row.  There is no problem with using whatever way as long as you escape your string correctly and in this case since the string would be numeric it wouldn't contain single or double quotes inside it.
0
 
LVL 59
ID: 23586003
<<Right, so the same thing applies if it has double quotes inside it and you used double quotes to quote thes tring.>>
  True, but it would be rare to find a quote character inside a string already, where as typically you will see a apostrophe as part of a name.  Given that and being that most like to do something in a consistent way when coding (at least I do), then it's best to avoid the use of the apostrophe.
  There is also readability to consider.    When your working with something like this:
 '"
With certain fonts and sizes, it's almost impossible to tell if that's an apstrophe followed by a quote or a quote followed by an apostrophe.  Since I've spent way too much time in the past debugging things like that, my preference is to use the Chr$(34) method, which is easy to read.
  It's only draw back is that to someone else reading your code, it might not be obvious what is being done if you don't know what Chr$(34) is right off.
FWIW,
JimD.
0
 
LVL 75
ID: 23586083
"You need to properly escape the string you're using by replacing any single quotes in the string (if you use single quotes for quoting) with two single quotes in a row.'

Unfortunately ... when your data is *already* stored like that in a text field in a table, you don't have the luxury of escaping the single quote dynamically. Hence the issue.

If you want to avoid the problem, do not use single quotes in criteria expressions.

mx
0
 
LVL 75
ID: 23586095
"but it would be rare to find a quote character inside a string already,"

Way more common than you would think.  You have a text field on a form for last name.
User enters O'Reilley
There you go.
0
 
LVL 59
ID: 23586107
<<
 "but it would be rare to find a quote character inside a string already,"

Way more common than you would think. You have a text field on a form for last name.
User enters O'Reilley
There you go.>>
That's not a quote ("), it's an apostrophe (').
JimD.
0
 
LVL 75
ID: 23586138
It's a single quote ... '

mx
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 23586453
It may arguably happen more often with single quotes in a string but it happens with either.  In any case if you don't know the value of a string you're using in a query you should do this:

"dbfield='" & Replace(strLookupValue, "'", "''") & "'"

That works 100%

or if you rather for double quotes:

"dbfield=" & chr(34) & Replace(strLookupValue, chr(34), chr(34)&chr(34)) & chr(34)

0
 
LVL 59
ID: 23589971
MX,
<<It's a single quote ... ' >>
Not within a name its not. It's an apostrophe. The only place "single quotes" are used is within text in which a quote appears that refers to another quote.
kmslogic,
<<It may arguably happen more often with single quotes in a string but it happens with either. In any case if you don't know the value of a string you're using in a query you should do this:

"dbfield='" & Replace(strLookupValue, "'", "''") & "'"

That works 100%

or if you rather for double quotes:

"dbfield=" & chr(34) & Replace(strLookupValue, chr(34), chr(34)&chr(34)) & chr(34)
>>
Yes, your right of course. It can happen with either. But 99% of the time, your not going to find a quote character (") in a string, so to me it never seemed worth the effort to go through typically.
I'd grant you to that the the only time you'll run into an apostrophe is when dealing with a name, so you could stick to doing it as you posted originally unless you are dealing with a name field. However, since I like to stay consistent and the use of chr$(34) to place a quote character really doesn't "cost" anything more then a few extra keystrokes on my part, it's the method I prefer to use as I can apply it consistently without a lot of extra effort or overhead.
JimD.
0
 

Author Comment

by:ssmith94015
ID: 23590445
W\hoa!  Thanks for the dialogue!  My workaround was to use the DLOOKUP that was in one of the unbound controls on the parent that refereneced the calling form's email field.  I am at home so do not have the exact code but it was a DLOOKUP("EmailAddress", "tblEmailAddresses", "BR_NBR = " & FORMS!frmW8Letter.email) which worked.  Don't ask me why.  But when I get a chance, after the time constraints, I will try some of the suggestions.

Thank you all very much for the information and lesson, I appreciate it all.
0
 
LVL 59
ID: 23590729
<<W\hoa! >>
  Never hurts to bash things around; even old dogs can learn new tricks<g> and I like to try to learn something new each day.
JimD.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 23591143
<>

You can call it an apostrophy but it is the same character on the keyboard as the 'single quote', and it's always worth the trouble to do it the way that works every time!
0
 
LVL 75
ID: 23592664
"You can call it an apostrophy but it is the same character on the keyboard as the 'single quote', "

Exactly.  An apostrophe is a single quote!

"use of chr$(34) to place a quote character really doesn't"
Chr(34) is a double quote.  Chr(39) is a single quote.
But that's not the issue I'm talking about per se.  Yes, I use Chr(34) for charity in posting and usually in code.

The issue is ... doing this with single quotes in the criteria string:

DLookup("EmailAddressTo", "tblEmailMessages", "BR_NBR = '" & strMaxBranch & "' ")

This was a tip I found in an Access book a couple of years ago, with several examples and the reasons.  I also saw this in some online tip once somewhere.  The issue is ... when you have stored text that has single quotes, such as the example I gave above.  I can't remember what it said about double quotes, although somewhat more rare I suppose in a 'normal' text string.  

I will dig up the tip in the book tonight ... and report back.

mx

0
 

Author Comment

by:ssmith94015
ID: 23605472
MX, I am looking forward to the tip.  This has really given me a lesson and I appreciate all of you sharing your experience and suggestions.
0
 
LVL 75
ID: 23605521
Sorry ... I spaced off and forgot to look it up in one of two books I have ... will do tonight.

mx
0
 
LVL 75
ID: 23614528
Breaking News:
I found the Single Quote tip ... and will post all the info as soon as I get a break here at work.  It's pretty interesting and straight forward.

mx
0
 
LVL 75
ID: 23625637
As promised ... here is the drill on using single quotes in criteria expressions.
******
This tip from a book titled Fixing Access Annoyances (published in 2006) by Phil Mitchell and Evan Callahan, OReilly is the publisher.  

BTW & its a VERYcool book with loads of cool, not so obvious tips & and stuff that I have forgotten!   It also has a nice Appendix with some good information.

This tip is from a section called Apostrophe Errors, starting on page 301.  Im going to paraphrase somewhat, because there is quite a bit of text.

THE ANNOYANCE:
I have a combo box that finds records on our employee form. But we get a syntax error for any employee whose name contains an apostrophe!

THE FIX

When names like OBrien, OMalley, OBama, ORight and Childrens Hospital cause an error such as Syntax error (missing operand) in query expression & its a sure bet that youve used an apostrophe (single quote) in your string criteria. Its an easy mistake for a beginning programmer to make.

Heres whats going on:

Your combo box contains a list of last names, to be used in a criteria expression in code, that might look like this:

Dim strCriteria As String
strCriteria = [lastName] =  & Me.cboLastName & 

But, since all those quotation marks are confusing to look at, you may be tempted to use single quotes embedded directly in the quoted string:

strCriteria = [lastName] =  & Me.cboLastName & 

This is cleaner, and it works because youre allowed to quote the target value in single quotes instead of double quotes, for example:

[LastName] = Murphy.

**However**, this trick fails if your target value itself contains a single quote & i.e., an apostrophe & because Accesss parser cant tell where your string ends !!

The moral of the story:  Dont use single quotes when your construct string criteria.   Use double quotes.

*** End Of Article / Tip ***

I prefer to use Chr(34) to represent a double quote  for clarity in posting  and in general, like so:

strCriteria = [lastName] =  & Chr(34)  & Me.cboLastName & Chr(34)

mx

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
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 …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

621 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