We help IT Professionals succeed at work.

Dlookup Won't Return value

Medium Priority
495 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

Comment
Watch Question

Commented:
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.
Sandra SmithRetired

Author

Commented:
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.
Retired
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sandra SmithRetired

Author

Commented:
I am requesting to simply close this question.

Commented:
What was your workaround?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
This should work ... IF ... strMaxBranch is a String value (?)

strTo = DLookup("[EmailAddressTo]", "[tblEmailMessages]", "[BR_NBR] = " & Chr(34)  & strMaxBranch & Chr(34) )
 
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Note:

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

mx

Commented:
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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
The problem comes when the text string itself (eg strMaxBranch ) contains single quotes, for example

O'Reilly

mx

Commented:
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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<
 "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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
It's a single quote ... '

mx

Commented:
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)

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Sandra SmithRetired

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.

Commented:
<>

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!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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

Sandra SmithRetired

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Sorry ... I spaced off and forgot to look it up in one of two books I have ... will do tonight.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.