string with quotes

[Stefania Scott]

How do I resolve the problem of passing a string that has quotes
within in a SQL statement? Sometimes the string contains a single
quote (') and some others it contains the double quote (")?

Any clue...

Thank you,

Stefania

[Douglas J. Steele]

Depends on how you're delimiting the string in your SQL statement.

If you're using a single quote to delimit, you need to change all
occurrences of single quotes in the string to two single quotes in a row:

"... WHERE MyName = '" & Replace(strValue, "'", "''") & "'"

Exagerated for clarity:

"... WHERE MyName = ' " & Replace(strValue, " ' ", " ' ' ") & " ' "

On the other hand, if you're using a double quote to delimit, you need to
change all occurrences of double quotes to two double quotes in a row:

"... WHERE MyName = " & Chr$(34) & Replace(strValue, Chr$(34), Chr$(34) &
Chr$(34)) & Chr$(34)

(In case you weren't aware, Chr$(34) is the same as ")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Stefania Scott" <st******@u.arizona.edu> wrote in message
news:3e**************************@posting.google.c om...

How do I resolve the problem of passing a string that has quotes
within in a SQL statement? Sometimes the string contains a single
quote (') and some others it contains the double quote (")?

Any clue...

Thank you,

Stefania

[stefania]

Doug,
Thank you for your reply.
My problem is that I cannot change the quotes within the string because
they have a meaning. Infact the string is the translation of a number to
be transformed in a barcode (code 128). So, maybe the solution is that I
need to search the string and if the string contains a double quote use
the single quote and viceversa. However, the problem rise if the string
contains both quotes and in this case I am lost.

Thanks again,

stefania

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Yannick Turgeon]

If you double your single quotes in your SQL statement, it in fact insert
only one single quote in the DB. The doubled single quote only means "don't
stop the string here and replace these doubled single quotes by only one
before processing the SQL statement" instead of "double this single quote
and insert them in order to my data to be correct". Not sure I'm very clear!
When retreiving your data, there will be only one single quote in it. HTH.

Yannick

"stefania" <st******@italy.it> wrote in message
news:40***********************@news.usenetmonster. com...

Doug,
Thank you for your reply.
My problem is that I cannot change the quotes within the string because
they have a meaning. Infact the string is the translation of a number to
be transformed in a barcode (code 128). So, maybe the solution is that I
need to search the string and if the string contains a double quote use
the single quote and viceversa. However, the problem rise if the string
contains both quotes and in this case I am lost.

Thanks again,

stefania

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!