Did you correct for the word-wrap in Graham's code?
The 3 lines of code
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"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11936-how-strip-out-non-numeric-characters.html
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"tom"
wrote in message
news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy .net...
> The code wouldnt compile
>
>
> "Graham R Seach" wrote in message
> news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
> > 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"
> > 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
> > ---------------------------
> >
> > "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.
> >>
> >
> >
>
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936