Trying to remove a ' from a string

[tmcrouse]

I am using Access 2010 and trying to do a replace to remove a ' from a string and it does not work. I am using:



replace([myfield], "'", "")

It runs the query but does not work as I still see:

In the last 6 months, did you get information or
help from your health plan’s customer service?

I have several fields in this large database that have the ' and I need to remove them so it says plans or doctors instead of plan's or doctor's. Just as an example. I need to do this because this data is actually going into SQL and SQL uses ' ' to notate strings. So if I move this data into SQL I will see:

In the last 6 months, did you get information or
help from your health plan’s customer service? and where I see plan's it places plan in black and then ' in red and then s in black. I am not sure if anyone out there knows how SQL Server works but I have to remove ' from anything I have because of how SQL Server operates.

[JeroenMioch]

Can you give us the SQL code ?
I am convinced there is a way to get around this by making SQL understand that the bit with the Appostrophe is text and not code.

[tmcrouse]

update
mytable
set
question ='In the last 12 months, did you have an illness, injury, or condition that needed care right away in a clinic, emergency room, or doctors office?'
where
question ='In the last 12 months, did you have an illness, injury, or condition that needed care right away in a clinic, emergency room, or doctor's office?'
update my table
set question = '
In the last 12 months, did you look forany information in written materials oron the Internet about how your health plan works?'
where question = '
In the last 12 months, did you look forany information in written materials oron the Internet about how your health plan's works?

There are other questions. I typed this directly in the SQL Server. I want to remove the ' from any of the sentences because of the fact that SQL treats ' as the way to say between this is a string. Like if I name Year as a nchar(10) and I say 'Year' my field will show just Year and treat it as text.

[ItsMe]

Here is an example of using an Escape Character in Transact SQL for a single quote.
http://blog.sqlauthority.com/2008/02...racter-string/

[tmcrouse]

Here is an example of using an Escape Character in Transact SQL for a single quote.
http://blog.sqlauthority.com/2008/02...racter-string/

will try that thanks

[June7]

In some of those examples, the apostrophe is correct grammar but you want to remove all regardless?

Instead of removing the apostrophe, I force the SQL to accept it as text instead of processing it as special character (delimiter).

Replace([myfield], "'", "''")

Example from my db:

CurrentDb.Execute "INSERT INTO VibReportData(LabNum, StateNum, ProjectName, Metric, Density0)" & _
" VALUES('" & rs!LABNUM & "', '" & rs!StateNum & "', '" & Replace(rs!ProjectName, "'", "''") & "', " & rs!Metric & ", '" & RRound(dblDensC, IIf(rs!Metric = True, 0, 1)) & "')"

However, seems your code to remove the character should also work so something else is going on. As requested, provide the attempted SQL statement for analysis if you need more help. Are you trying to run an Access UPDATE object to correct the text in table? How many records are involved? Is this a 1-time event? Maybe just use the intrinsic Find/Replace dialog? Do you want to remove the s as well?