Tom,
Firstly, add the following to a standard module:
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 = "\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)
> 0, "|", "") & "\W"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11936-how-strip-out-non-numeric-characters.html
End If
'Debug.Print "Regular Expression: " & sRegExpr
With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With
Set objRegEx = Nothing
End Function
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
"tom"
wrote in message
news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>I need to strip out all the non numeric characters
> in a query. Please advise how to do this.
>