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

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

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

but it shows in red as incorrect
"jv" <julie_vazquez (AT) hotmail (DOT) com> wrote

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!

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" <julie_vazquez (AT) hotmail (DOT) com> 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!

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:

Paradigm wrote:

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,""",".")