How to delete certain characters from a field

[Guest]

I have a Name field in a table that contains special characters like commas,
hyphens, quotes, etc... I need to be able to have it just be alpha so I can
compare it to another name field and return matches. Can you show me an
example query to get this done.

Existing Data:
Smith, John

Needs to look like:
Smith John

Thanks.

 

[Lynn Trapp]

This will work with your example. You will need to modify it for other
characters.

Left([YourNameField],InStr([YourNameField],",")-1) &
Mid([YourNameField],InStr([YourNameField],",")+1)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Douglas J Steele]

An alternative to Lynn's suggestion is to use the Replace function:

Replace([YourNameField], ",", "")

Again, though, you will need to modify it for other characters. One
advantage, though, is that it nests easily:

Replace(Replace(Replace([YourNameField], ",", ""), "-", ""), ";", "")

will removes commas, hyphens and semi-colons.