Try this.
Call it in VBA like so, StripEx("sometext123", se_AllButNum)
Call it in SQL like so, StripEx([some field name], 32)
Public Enum StripType
se_Char = &H1
se_Num = &H2
se_NonWord = &H4
se_Space = &H8
se_AllButChar = &H10
se_AllButNum = &H20
se_Custom = &H40
End Enum
Public Function StripEx(sText As String, lExpr As StripType, Optional
sUsrExpr As String = "") As String
' Function to selectively strip any combination of the following from a
string:
' Characters (a-z or A-Z)
' Numbers (0-9)
' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
' Spaces
'-------------------------------------------------------------------------------
Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.RegExp")
If lExpr And se_Custom Then
'User-defined expression
sRegExpr = sUsrExpr
ElseIf lExpr And se_AllButChar Then
'Strip out everything except alpha characters
sRegExpr = "[^a-zA-Z]"
ElseIf lExpr And se_AllButNum Then
'Strip out everything except numbers
sRegExpr = "[^0-9.-]" '"\D[.]"
Else
'Custom strip
If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr
With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With
Set objRegEx = Nothing
End Function
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia