Remove Quoations from Names in ACCESS 2013

[Huizar]

My Access table has a column with employee names. Some names have quotation marks and some don't. How can I remove the quotation marks that surround the names on my table? Keep in my mind it's my main table I run to create my queries.


Thanks

 

[Huizar]

Hey Joe,

I tried doing the sql but it did not work for me. The field name that I'm using is Full Name so i adjust accordingly. Not sure what i'm doing wrong.

 

[xenou]

Try:

UPDATE TableName
SET [Full Name]= Replace([Full Name],Chr(34),"")

 

[Huizar]

Hey Xenou,

it says Syntax Error when I put in SQL. Here is what i put:

UPDATE A01_Workload_t SET;
SET [Full Name]= Replace([Full Name],Chr(34),"")

Thanks

 

[Joe4]

You have an extra word SET and semi-colon at the end of your UPDATE line. Get rid of those.

[COLOR=#333333]UPDATE A01_Workload_t[/COLOR]
[COLOR=#333333]SET [Full Name]= Replace([Full Name],Chr(34),"")[/COLOR]

 

[Huizar]

Hey Joe,

That worked. I'm curious to know why Access came back came back with the message that it didn't update 17 field(s) due to type conversion failure, 0 record(s) due to lock violations, etc. Would this affect the query i just ran?


Thanks,

 

[Joe4]

If you have null/blank values in your Full Name field, it is telling you cannot update those. You can update your query to ignore those null/blank records, then you won't get those error messages.

UPDATE A01_Workload_t SET A01_Workload_t.[Full Name] = Replace([Full Name],Chr(34),"")
WHERE A01_Workload_t.[Full Name]<>"" And Not (A01_Workload_t.[Full Name] Is Null);

 

[Huizar]

If you have null/blank values in your Full Name field, it is telling you cannot update those. You can update your query to ignore those null/blank records, then you won't get those error messages.

UPDATE A01_Workload_t SET A01_Workload_t.[Full Name] = Replace([Full Name],Chr(34),"")
WHERE A01_Workload_t.[Full Name]<>"" And Not (A01_Workload_t.[Full Name] Is Null);

Thanks for your help.

 

[Huizar]

Try:

UPDATE TableName
SET [Full Name]= Replace([Full Name],Chr(34),"")

Thanks for you help.

 

[Joe4]

You are welcome!

In working with these kind of UPDATE queries, I find the following methodology typically works best for me.
First, write a simple SELECT query that returns JUST the records you want to update (this is where the criteria comes in). The more you can weed out, the less chance you will get weird error messages you don't care about.
Once you have that, then change it to an UPDATE query.