Remove single qoute in the data

[accessfever]

I have a text column which I want to remove the single qoute(s) in the records. What is the command to remove the single quote?

E.g 1.

Original data = 1,234,567 EA
New data wanted = 1234567 EA


E.g 2.

Original data = 1,234 ROL
New data wanted=1234 ROL
Any idea?

 

[SteveSchapel]

Accessfever,

Those are not single quotes. They are commas.

You can run an Update Query on the table. Update [TheNameOfYourField] to:
Replace([TheNameOfYourField],",","")

 

[vbaInet]

And in case you want to still keep the original data but also be able to present it without the commas, use Steve's code in a SELECT query as an aliased field.

In a new field of the query:

AliasFieldName: Replace([TheNameOfYourField],",","")

http://www.techonthenet.com/access/functions/string/replace.php

 

[ChrisO]

Another thing to consider is if the data in non-atomic.

Would your database be better served if 1,234,567 was in one field and EA in another?

If 1,234,567 is in a numeric field then there will be no formatting applied in the field.
The formatting can still be applied, based on regional settings, when displayed.

 

[vbaInet]

Hey Chris, long time no speak. Glad to see you're still here.

 

[ChrisO]

Still here…occasionally.

Been a bit busy of late, trying to bring many A97 databases up to A2K3 FE’s and SQL Server BE.

Not going too well as both speed and security seem to be a sticking point at the moment.

But that, as they say, is what we are paid to do…

 

[vbaInet]

Been a bit busy of late, trying to bring many ...

Certainly not a fun task then I would imagine?

Good luck with it.

 

[accessfever]

Thanks for you guys' help! It works.