Does RemoveAlpha work?

[Patrick]

I remember reading that if you used RemoveAlpha([xxx]) in
an update query, Access would remove all non-alphanumeric
characters from the data in column xxx. I can't get it to
work on Access 97, 2000, or 2002.

Has anyone had any luck with this? It'd be perfect for
what I'm trying to do.

Thanks,
Patrick

 

[Dan Artuso]

Hi,
Never heard of it. I can tell you it's not part of 97.

Here is something to get you started:

Public Function RemoveAlpha(expression As String) As String
Dim i As Integer
Dim intChar As Integer
Dim strChar As String
Dim strTemp As String

For i = 1 To Len(expression)
strChar = Mid(expression, i, 1)
intChar = Asc(strChar)
Select Case intChar
Case 32, 48 To 57, 65 To 90, 97 To 122
strTemp = strTemp & strChar
End Select
Next i

RemoveAlpha = strTemp


End Function

You can call this from your query. You didn't mention if there are spaces in your data.
The above leaves the spaces in, but this means you can end up with more than one space
between words. Also, if you have lots of data, it may take a while to run.