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

SQL statement with quotation marks

P: n/a
I am trying to create a recordset where some text fields are matching. The
problem is that some of the text fields contain quotation marks. I have
tried to create the sql string using replace eg.
"SELECT * FROM MYTABLE WHERE REPLACE(MYFIELD,"""",".") = """ &
REPLACE(MYTEXT,"""",".") & """"
but this does not work. Trying to replace the quotation marks with some
other character in this case the stop
How can I embed the quotation marks in the sql string?

Alex
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
jv
Alex,

I also use the replace() function to eliminate quotations and
apostrophes for SQL statement and it works great for me (I actually
replace it double apostrophes, which becomes a single apostrophe).

Why is the WHERE clause REPLACE(MYFIELD,"""",".")= """ ?

Trying putting a breakpoint in VBA and then evaluate that SQL string in
the Immediate window.

Good Luck

Nov 13 '05 #2

P: n/a
My staement is
STRSQL = "SELECT * FROM TBLINMAIL WHERE replace(Subject,"""",".") = """ &
Replace(mySubject, """", ".") & """"

but it shows in red as incorrect
"jv" <ju***********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Alex,

I also use the replace() function to eliminate quotations and
apostrophes for SQL statement and it works great for me (I actually
replace it double apostrophes, which becomes a single apostrophe).

Why is the WHERE clause REPLACE(MYFIELD,"""",".")= """ ?

Trying putting a breakpoint in VBA and then evaluate that SQL string in
the Immediate window.

Good Luck

Nov 13 '05 #3

P: n/a
Try this:

STRSQL = "SELECT * FROM TBLINMAIL WHERE Subject = '" &
Replace(mySubject, """", ".") & "'"

I don't think you need to put replace() around the field name part of
the expression, only around the value part.

Good Luck.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Yes that works BUT I also need to replace the subject in the string with a
replace
eg.
STRSQL = "SELECT * FROM TBLINMAIL WHERE REPLACE(SUBJECT,"""",".") = " ETC
and it seems that I cannot use the replace in a query
Alex

"julie vazquez" <ju***********@hotmail.com> wrote in message
news:41**********@127.0.0.1...
Try this:

STRSQL = "SELECT * FROM TBLINMAIL WHERE Subject = '" &
Replace(mySubject, """", ".") & "'"

I don't think you need to put replace() around the field name part of
the expression, only around the value part.

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

Nov 13 '05 #5

P: n/a
I don't see why you need to put a replace around your table field name.
It doesn't make sense.

But if you wish, you can put it like this:

strSQL = "SELECT * FROM TBLINMAIL WHERE replace(Subject,'""','.') = '" &
Replace(mySubject, """", ".") & "'"


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

P: n/a
Do a google search for quotes in strings. Also check The Access Web
which has an elegant solution using Const cQuote = """"

Goog luck!
julie vazquez wrote:
I don't see why you need to put a replace around your table field name. It doesn't make sense.

But if you wish, you can put it like this:

strSQL = "SELECT * FROM TBLINMAIL WHERE replace(Subject,'""','.') = '" & Replace(mySubject, """", ".") & "'"


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


Nov 13 '05 #7

P: n/a
Paradigm wrote:
I am trying to create a recordset where some text fields are matching. The
problem is that some of the text fields contain quotation marks. I have
tried to create the sql string using replace eg.
"SELECT * FROM MYTABLE WHERE REPLACE(MYFIELD,"""",".") = """ &
REPLACE(MYTEXT,"""",".") & """"
but this does not work. Trying to replace the quotation marks with some
other character in this case the stop
How can I embed the quotation marks in the sql string?

Alex


Why are you needing that? And your logic of trying to replace quotes
with a period and comparing them to a quote doesn't make sense. Check
the following line....it's more to what you want.

"SELECT * FROM MYTABLE WHERE REPLACE(MYFIELD,"""""",""."") =
REPLACE(MYTEXT,"""""",""."")"
To verify the above, Go to the Debug/Immediate Window. Enter
v = "SELECT * FROM MYTABLE WHERE REPLACE(MYFIELD,"""""",""."") =
REPLACE(MYTEXT,"""""",""."")"

? v

this will echo back
SELECT * FROM MYTABLE WHERE REPLACE(MYFIELD,""",".") =
REPLACE(MYTEXT,""",".")

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.