Remove all Punctuations, special characters and must be All Caps Query

[usmananyc]

Hello Everyone,



I need some help with an Access Query. I have a very large dataset with customer name, address field, etc that needs to be cleaned up. Some fields are ok but others contain Punctuations (!,.!?/) and some contain special Characters (@#$%&*).

I basically need a way to clean these all up with just a single space between each and all must be in CAPS. Also if its not too much trouble i also need a way to omit "A, an, the" at the beginning of the customer name.

Any SQL Query or Macro would be greatly appreciated. Thank you!

[ranman256]

convert to caps with an update query...
update [field] to UCase ([FIELD] )

As for the special characters,
I have a macro, mFixBadChars
in it is about 6 update queries to correct the field.
the queries look like:
UPDATE tData SET [MyMemo] = Replace([MyMemo],"@"," ");
UPDATE tData SET [MyMemo] = Replace([MyMemo],"!"," ");