I've got a Customer Number stored as text such as: 1003806N2001
Is there a function I can use that would remove the alpha character
(leaving only the numeric characters), no matter where the alpha
character may appear in the string?
If it's always the same "N" you can use the Replace function if you
have access 2000 or later.
Replace([FieldName],"N","")
If the letter can vary, or if there can be more than one letter, i.e.
10025BG236UM89, then, in all versions of Access ....
Paste the following function into a module:
Public Function RemoveAlpha(strIn as String) as String
' To remove all Alpha characters from a string
If IsNull(strIn) Then
Exit Function
End If
Dim intX As Integer
Dim intY As Integer
Dim strOut As String
For intX = 1 To Len(strIn)
intY = Asc(Mid(strIn, intX, 1))
If intY >= 48 And intY <= 57 Then
strOut = strOut & Chr(intY)
End If
Next intX
RemoveAlpha = strOut
End Function
=============
You can call it from a query:
Exp:RemoveAlpha([FieldName])
