A VERY STRANGE QUESTION ABOUT SQL STRING in VBA ACCESS

[Palatini]

Hi guys I am writing a SQL string in VBA code for a database. it looks
like
strSql = "select * from tbl_patient where surname='" & rsVisaRep!
Surname & "'"
it gives the right strSql string, such as: select * from tbl_patient
where surname='Bush'
but when deal with some surnames like D'Angilo the SQL string is
not right.

How to fix it?
Thanks
Wayne

 

[strive4peace]

Hi Wayne,

try this instead:

"...surname=""" & rsVisaRep!Surname & """"

then, your text will be delimited with a double quote mark instead of a
single one so if it contains a single quote, there will not be a problem.

You must use 2 double quote marks if you want one if the string is
delimited with double quotes

Warm Regards,
Crystal

*
have an awesome day
*

 

[Palatini]

Hi Wayne,

try this instead:

"...surname=""" & rsVisaRep!Surname & """"

then, your text will be delimited with a double quote mark instead of a
single one so if it contains a single quote, there will not be a problem.

You must use 2 double quote marks if you want one if the string is
delimited with double quotes

Warm Regards,
Crystal

  *
    have an awesome day
  *





- Show quoted text -

DONE!
Thank you very much!

 

[Douglas J. Steele]

Just for the sake of completeness, be aware that you can still run into
problems using that approach (although it's unlikely with surnames). Let's
say you've got the name of businesses, and one happens to be

Fred's "Olde English Fish Shoppe"

You've got both single and double quotes there. You need to pick which one
you want to use as a delimiter, and then ensure you've doubled up on that
one:

Either

....CompanyName = """ & Replace(rsVisaRep!Surname, """", """""") & """"

(that's three double quotes before the first ampersand, four double quotes
as the 2nd parameter in the Replace function, six double quotes as the 3rd
parameter in the Replace function, and four double quotes after the second
ampersand)

or

....CompanyName = '" & Replace(rsVisaRep!Surname, "'", "''") & "'"

Exagerated for clarity, that's

....CompanyName = ' " & Replace(rsVisaRep!Surname, " ' ", " ' ' ") & " ' "

 

[strive4peace]

you're welcome, Wayne happy to help

Warm Regards,
Crystal

*
have an awesome day
*

 

[aaron.kempf]

I just think that if you used stored procedures, then you wouldn't
have to get all worked up about stuff like this.

-Aaron

 

[Tony Epton]

I've always used chr(34) both sides of the expression, because I feel
that it is much more readable.
Would I still have a problem with the expression below ?

Tony

 

[John W. Vinson]

I've always used chr(34) both sides of the expression, because I feel
that it is much more readable.
Would I still have a problem with the expression below ?

Tony

Yes, because Chr(34) and " are the same character. The doublequote before Olde
will be seen as the terminating quote of the string.

See the posted resources for getarounds. Doubling up the doublequotes may be
the best solution:

Chr(34) & Replace(strCriteria, Chr(34), Chr(34) & Chr(34)) & Chr(34)

will make it

WHERE BusinessName = "Fred's ""Olde English Fish Shoppe"""

which should work.