UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Quote Characters In The Where Condition Of Dlookup, Access 2010    
 
   
JohnvanSomeren
post Sep 5 2019, 10:16 AM
Post#1



Posts: 669
Joined: 31-August 12
From: London, England


After a three year break I must modify my Access program. I'm well into my dotage and cannot work out how to convert a Dloopkup command's Where condition parameter when the data type has changed from Long to String.
I need the correct VBA code and I would love a link to any instruction on the way to use quote characters in DLookup and other strings I build like filters.

Here is the original statement
CODE
    DoCmd.OpenForm "frmManageContacts", acNormal, , "ContactID=" & paramContactID, _
                    acEdit, acWindowNormal, Nz(paramContactID, 0)

The parameter paramContactID is now a String and I am having difficulty with coding the actual WHERE condition parameter. I know I need to add lots of quote characters, but I cannot visualise what the string ought to look like when it is passed to the function.

I can convert the OpenArgs parameter myself (I hope!)

Thanks.
John van Someren

Go to the top of the page
 
arnelgp
post Sep 5 2019, 10:21 AM
Post#2



Posts: 1,474
Joined: 2-April 09
From: somewhere out there...


CODE
DoCmd.OpenForm "frmManageContacts", acNormal, , "ContactID=" & Chr(34) & paramContactID & Chr(34), _
                    acEdit, acWindowNormal, Nz(paramContactID, "")

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
theDBguy
post Sep 5 2019, 10:47 AM
Post#3


Access Wiki and Forums Moderator
Posts: 76,405
Joined: 19-June 07
From: SunnySandyEggo


Hi John. Arnel’s code should work. Would the String value ever contain any apostrophe or double quote characters?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JohnvanSomeren
post Sep 5 2019, 10:54 AM
Post#4



Posts: 669
Joined: 31-August 12
From: London, England


Hi DBGuy,
Good thought, but the strings are formed from a short alpha prefix to the previous integer.

Glad to see that you are still patrolling the corridors of this web site. I remember only good responses from you when I was an Access neophyte six or seven years ago.

Thanks
John
Go to the top of the page
 
theDBguy
post Sep 5 2019, 11:35 AM
Post#5


Access Wiki and Forums Moderator
Posts: 76,405
Joined: 19-June 07
From: SunnySandyEggo


Hi John. Thanks for your comments. We’re still here and happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post Sep 6 2019, 07:09 AM
Post#6


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


QUOTE
"ContactID=" & Chr(34) & paramContactID & Chr(34)

I think that code will leave off the closing quote, but I'm not sure. It needs to be this:

"ContactID=""" & paramContactID & """""

or (spaces between quotes added for clarity, but should be removed in practice):

"ContactID=' " & paramContactID & " ' "

I'm sure there is a way using Chr(34), but I'm not exactly sure what it is. My solution to quotes within quotes has been, for some years now, to use this wrapper function QuoteFix, posted in the UA function library. With the function in place:

"ContactID = " & QuoteFix(paramContactID)

Finally, I will point out that paramContactID, assuming it is a string or variant variable, could be assembled with leading and trailing quotes.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 12:31 PM