How can I remove non-letters from words in Access?

C

Cainman

I am searching for a way to remove all non-letters (i.e. slashes, periods,
spaces, commas, etc.) from words in Access running a query.

ex. remove periods, forward slashes, spaces, and tildas from
"32.HCGS.974 23/NW~MP" to get "32HCGS97423NWMP"

Thank you.
 
M

Matt Williamson

I whipped up a quick function that will do what you want. Just add a module
and put this code in

Function MultiReplace(sInput As String) As String

Dim s() As String, i As Long
Dim sDelim As String
sDelim = "~|\|.|/| "
s = Split(sDelim, "|")
For i = LBound(s) To UBound(s)
sInput = Replace(sInput, s(i), "")
Next

MultiReplace = sInput

End Function

You can add more items to remove by adding them to the sDelim = line just
seperate them with a |

Now just add that function before your Field name in your query.

HTH

Matt
 
C

Cainman

Thanks Matt that worked perfectly!

Adam Kastan

Matt Williamson said:
I whipped up a quick function that will do what you want. Just add a module
and put this code in

Function MultiReplace(sInput As String) As String

Dim s() As String, i As Long
Dim sDelim As String
sDelim = "~|\|.|/| "
s = Split(sDelim, "|")
For i = LBound(s) To UBound(s)
sInput = Replace(sInput, s(i), "")
Next

MultiReplace = sInput

End Function

You can add more items to remove by adding them to the sDelim = line just
seperate them with a |

Now just add that function before your Field name in your query.

HTH

Matt
Click to expand...
 
Top