Access query - How do I replace a double & single quotation character in a field

Question

In a table I receive data that contains what appear to be both double and single quotation marks in a text field.

I set up 2 queries:

• UPDATE CSCust SET CSCust.final_company = Replace([final_company],Chr(34),"");
• UPDATE CSCust SET CSCust.final_company = Replace([final_company],Chr(39),"");

but the characters remain.  So what is another approach to getting rid of these characters?

Dave

Answer 1

How are you running the Update query? Note that opening it in datasheet view does NOT actually execute it - instead it shows the data prior to its execution. The ! icon on the toolbar will run the query (you should get a message box saying "this query will make permanent changes...")

I'd suggest as SQL:

UPDATE CSCust

SET CSCust.final_company = Replace(Replace([final_company], Chr(34), ""),Chr(39), "")

WHERE final_company LIKE "*" & Chr(34) & "*"

OR final_company LIKE "*" & Chr(39) & "*";

to limit the update to those records which need updating.

Answer 2

Also replace the "curly" quotes Chr(145), Chr(146), Chr(147) and Chr(148).

Answer 3

I did find it is a ASC 39, so why does this not fix it?

UPDATE CSCust SET CSCust.final_company = Replace([final_company],Chr(39),"")

WHERE (((CSCust.final_company)>''));

Answer 4

What does the x represent?

Answer 5

First you have to determine exactly what the characters are.  There are more than one that appears to be a double quote.   Use the ASC function to find the Chr value.     Asc(Mid([TextField], x, 1))