469,342 Members | 5,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

SQL statement with quotation marks

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
7 11184
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
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
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
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
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
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
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.

Similar topics

8 posts views Thread by Stephen Poley | last post: by
63 posts views Thread by Tristan Miller | last post: by
reply views Thread by Aaron Deskins | last post: by
31 posts views Thread by The Bicycling Guitarist | last post: by
12 posts views Thread by Adam Right | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.