Difficulty with search string

  • Thread starter Thread starter JonnyB
  • Start date Start date
J

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
 
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.
Click to expand...
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 <--
 
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

Stefan Hoffmann said:
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.
Click to expand...
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 <--
Click to expand...
 
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
 
Back
Top