Difficulty with search string

[JonnyB]

Hi

Having a problem with a search string routine that checks to see whether a
name entered is a diplicate. I build the strings as:

stLinkCriteria = "[OrgName]=" & "'" & strOrgName & "'"

which works fine as long as there is no apostrophe in the name. If I input a
name such as Women's Network, I get a Run-time error 3075: missing operator.
I know that I need to alter the way in which quotes appear, but I am
struggling. Can anyone help me please.

Thanks

JonnyB

 

[Douglas J. Steele]

Check my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Stefan Hoffmann]

hi Johnny,

which works fine as long as there is no apostrophe in the name. If I input a
name such as Women's Network, I get a Run-time error 3075: missing operator.
I know that I need to alter the way in which quotes appear, but I am
struggling. Can anyone help me please.

You need to escape the used string delimiter by doubling it, e.g.

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

and

stLinkCriteria = "[OrgName]=" & SQLQuote(strOrgName)



mfG
--> stefan <--

 

[JonnyB]

Hi Stefan

Great, both yours and Douglas' approach worked. I have gone for your
universal approach since I use such strings in other forms.

Thanks for your help.

Jonny

hi Johnny,

which works fine as long as there is no apostrophe in the name. If I input a
name such as Women's Network, I get a Run-time error 3075: missing operator.
I know that I need to alter the way in which quotes appear, but I am
struggling. Can anyone help me please.

You need to escape the used string delimiter by doubling it, e.g.

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

and

stLinkCriteria = "[OrgName]=" & SQLQuote(strOrgName)



mfG
--> stefan <--

 

[JonnyB]

Hi Doug

Thanks for your help, your sugestion worked. Your May 2004 article also
helped me to resolve this issue once and for all!

Thank you

Jonny