By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

string with quotes

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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!
Nov 13 '05 #3

P: n/a
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!

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.