Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to strip out non numeric characters in a query

Discussion in 'Information Technology' started by tom, Jul 28, 2006.

  1. tom

    tom
    Expand Collapse
    Guest

    I need to strip out all the non numeric characters
    in a query. Please advise how to do this.
     
  2. Loading...


  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Here is a function that will do this:

    '********************************
    '* *
    '* Fxn StripAllNonNumericChars *
    '* *
    '********************************

    ' ** This function strips all nonnumeric characters from a text string.

    Function StripAllNonNumericChars(strOriginalString As String) As String
    Dim blnStrip As Boolean
    Dim intLoop As Integer
    Dim lngLoop As Long
    Dim strTemp As String, strChar As String
    On Error Resume Next
    For lngLoop = Len(strOriginalString) To 1 Step -1
    blnStrip = True
    strChar = Mid(strOriginalString, lngLoop, 1)
    For intLoop = Asc("0") To Asc("9")
    If strChar = Chr(intLoop) Then
    blnStrip = False
    Exit For
    End If
    Next intLoop
    If blnStrip = False Then strTemp = strChar & strTemp
    Next lngLoop
    StripAllNonNumericChars = strTemp
    Exit Function
    End Function


    --

    Ken Snell
    <MS ACCESS MVP>

    "tom" <Spamblocker@ameritech.net> 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.
    >
     
  4. Tom

    Tom
    Expand Collapse
    Guest

    WOW!!!!!

    That is a real mouthfull.

    What I really need is the following:

    substring(string from pattern)

    Extract substring matching POSIX regular expression


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    > Here is a function that will do this:
    >
    > '********************************
    > '* *
    > '* Fxn StripAllNonNumericChars *
    > '* *
    > '********************************
    >
    > ' ** This function strips all nonnumeric characters from a text string.
    >
    > Function StripAllNonNumericChars(strOriginalString As String) As String
    > Dim blnStrip As Boolean
    > Dim intLoop As Integer
    > Dim lngLoop As Long
    > Dim strTemp As String, strChar As String
    > On Error Resume Next
    > For lngLoop = Len(strOriginalString) To 1 Step -1
    > blnStrip = True
    > strChar = Mid(strOriginalString, lngLoop, 1)
    > For intLoop = Asc("0") To Asc("9")
    > If strChar = Chr(intLoop) Then
    > blnStrip = False
    > Exit For
    > End If
    > Next intLoop
    > If blnStrip = False Then strTemp = strChar & strTemp
    > Next lngLoop
    > StripAllNonNumericChars = strTemp
    > Exit Function
    > End Function
    >
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "tom" <Spamblocker@ameritech.net> 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.
    > >

    >
    >
     
  5. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Unfortunately, I am not knowledgeable about REGEX and similar types of
    pattern matching. John Nurick and Douglas Steele (both MVPs) are, however; I
    will "ping" them to take a look at this thread.


    --

    Ken Snell
    <MS ACCESS MVP>

    "Tom" <TOM@spam.net> wrote in message
    news:5uGdnV9vrtjbzQbZnZ2dnUVZ_rWdnZ2d@comcast.com...
    > WOW!!!!!
    >
    > That is a real mouthfull.
    >
    > What I really need is the following:
    >
    > substring(string from pattern)
    >
    > Extract substring matching POSIX regular expression
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    >> Here is a function that will do this:
    >>
    >> '********************************
    >> '* *
    >> '* Fxn StripAllNonNumericChars *
    >> '* *
    >> '********************************
    >>
    >> ' ** This function strips all nonnumeric characters from a text string.
    >>
    >> Function StripAllNonNumericChars(strOriginalString As String) As String
    >> Dim blnStrip As Boolean
    >> Dim intLoop As Integer
    >> Dim lngLoop As Long
    >> Dim strTemp As String, strChar As String
    >> On Error Resume Next
    >> For lngLoop = Len(strOriginalString) To 1 Step -1
    >> blnStrip = True
    >> strChar = Mid(strOriginalString, lngLoop, 1)
    >> For intLoop = Asc("0") To Asc("9")
    >> If strChar = Chr(intLoop) Then
    >> blnStrip = False
    >> Exit For
    >> End If
    >> Next intLoop
    >> If blnStrip = False Then strTemp = strChar & strTemp
    >> Next lngLoop
    >> StripAllNonNumericChars = strTemp
    >> Exit Function
    >> End Function
    >>
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >> "tom" <Spamblocker@ameritech.net> 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.
    >> >

    >>
    >>

    >
    >
     
  6. Graham R Seach

    Graham R Seach
    Expand Collapse
    Guest

    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" <Spamblocker@ameritech.net> 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.
    >
     
  7. tom

    tom
    Expand Collapse
    Guest

    The code wouldnt compile


    "Graham R Seach" <gseach@accessmvp_REMOVE.com> 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" <Spamblocker@ameritech.net> 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.
    >>

    >
    >
     
  8. tom

    tom
    Expand Collapse
    Guest

    Your code works but when I try to use it in a join
    with a another table with text field, I am getting
    a data type mismatch. Some of the entries are coming
    up #error. Could that be the problem?


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    > Here is a function that will do this:
    >
    > '********************************
    > '* *
    > '* Fxn StripAllNonNumericChars *
    > '* *
    > '********************************
    >
    > ' ** This function strips all nonnumeric characters from a text string.
    >
    > Function StripAllNonNumericChars(strOriginalString As String) As String
    > Dim blnStrip As Boolean
    > Dim intLoop As Integer
    > Dim lngLoop As Long
    > Dim strTemp As String, strChar As String
    > On Error Resume Next
    > For lngLoop = Len(strOriginalString) To 1 Step -1
    > blnStrip = True
    > strChar = Mid(strOriginalString, lngLoop, 1)
    > For intLoop = Asc("0") To Asc("9")
    > If strChar = Chr(intLoop) Then
    > blnStrip = False
    > Exit For
    > End If
    > Next intLoop
    > If blnStrip = False Then strTemp = strChar & strTemp
    > Next lngLoop
    > StripAllNonNumericChars = strTemp
    > Exit Function
    > End Function
    >
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "tom" <Spamblocker@ameritech.net> 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.
    >>

    >
    >
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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"
    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" <Spamblocker@ameritech.net> wrote in message
    news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy.net...
    > The code wouldnt compile
    >
    >
    > "Graham R Seach" <gseach@accessmvp_REMOVE.com> 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" <Spamblocker@ameritech.net> 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.
    > >>

    > >
    > >

    >
    >
     
  10. tom

    tom
    Expand Collapse
    Guest

    The problem is with the enum. I am using
    Access 97. I am worried about upgrading for
    fear of breaking the code I have there.

    Is there problems with upgrading from Access 97.


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:e9800OvlGHA.3576@TK2MSFTNGP04.phx.gbl...
    > 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"
    > 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" <Spamblocker@ameritech.net> wrote in message
    > news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy.net...
    >> The code wouldnt compile
    >>
    >>
    >> "Graham R Seach" <gseach@accessmvp_REMOVE.com> 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" <Spamblocker@ameritech.net> 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.
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  11. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try:


    Const se_Char As Long = &H1
    Const se_Num As Long = &H2
    Const se_NonWord As Long = &H4
    Const se_Space As Long = &H8
    Const se_AllButChar As Long = &H10
    Const se_AllButNum As Long = &H20
    Const se_Custom As Long = &H40

    Public Function StripEx(sText As String, lExpr As Long, Optional sUsrExpr As
    String = "") As String


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "tom" <Spamblocker@ameritech.net> wrote in message
    news:QSWmg.155912$F_3.109881@newssvr29.news.prodigy.net...
    > The problem is with the enum. I am using
    > Access 97. I am worried about upgrading for
    > fear of breaking the code I have there.
    >
    > Is there problems with upgrading from Access 97.
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:e9800OvlGHA.3576@TK2MSFTNGP04.phx.gbl...
    > > 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"
    > > 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" <Spamblocker@ameritech.net> wrote in message
    > > news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy.net...
    > >> The code wouldnt compile
    > >>
    > >>
    > >> "Graham R Seach" <gseach@accessmvp_REMOVE.com> 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" <Spamblocker@ameritech.net> 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.
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  12. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    ?? Using the function in a join in a query? Post the SQL statement that you
    created to do this so I can see what you mean.

    --

    Ken Snell
    <MS ACCESS MVP>

    "tom" <Spamblocker@ameritech.net> wrote in message
    news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    > Your code works but when I try to use it in a join
    > with a another table with text field, I am getting
    > a data type mismatch. Some of the entries are coming
    > up #error. Could that be the problem?
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    >> Here is a function that will do this:
    >>
    >> '********************************
    >> '* *
    >> '* Fxn StripAllNonNumericChars *
    >> '* *
    >> '********************************
    >>
    >> ' ** This function strips all nonnumeric characters from a text string.
    >>
    >> Function StripAllNonNumericChars(strOriginalString As String) As String
    >> Dim blnStrip As Boolean
    >> Dim intLoop As Integer
    >> Dim lngLoop As Long
    >> Dim strTemp As String, strChar As String
    >> On Error Resume Next
    >> For lngLoop = Len(strOriginalString) To 1 Step -1
    >> blnStrip = True
    >> strChar = Mid(strOriginalString, lngLoop, 1)
    >> For intLoop = Asc("0") To Asc("9")
    >> If strChar = Chr(intLoop) Then
    >> blnStrip = False
    >> Exit For
    >> End If
    >> Next intLoop
    >> If blnStrip = False Then strTemp = strChar & strTemp
    >> Next lngLoop
    >> StripAllNonNumericChars = strTemp
    >> Exit Function
    >> End Function
    >>
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >> "tom" <Spamblocker@ameritech.net> 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.
    >>>

    >>
    >>

    >
    >
     
  13. Tom

    Tom
    Expand Collapse
    Guest

    SELECT
    FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order Number] = Query2.Expr1;

    Query2 is as follows:

    SELECT StripAllNonNumericChars([Source of Order]) AS Expr1, invoice.[Invoice Number]
    FROM invoice;


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message news:#Wh4hCxlGHA.4196@TK2MSFTNGP03.phx.gbl...
    > ?? Using the function in a join in a query? Post the SQL statement that you
    > created to do this so I can see what you mean.
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "tom" <Spamblocker@ameritech.net> wrote in message
    > news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    > > Your code works but when I try to use it in a join
    > > with a another table with text field, I am getting
    > > a data type mismatch. Some of the entries are coming
    > > up #error. Could that be the problem?
    > >
    > >
    > > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    > >> Here is a function that will do this:
    > >>
    > >> '********************************
    > >> '* *
    > >> '* Fxn StripAllNonNumericChars *
    > >> '* *
    > >> '********************************
    > >>
    > >> ' ** This function strips all nonnumeric characters from a text string.
    > >>
    > >> Function StripAllNonNumericChars(strOriginalString As String) As String
    > >> Dim blnStrip As Boolean
    > >> Dim intLoop As Integer
    > >> Dim lngLoop As Long
    > >> Dim strTemp As String, strChar As String
    > >> On Error Resume Next
    > >> For lngLoop = Len(strOriginalString) To 1 Step -1
    > >> blnStrip = True
    > >> strChar = Mid(strOriginalString, lngLoop, 1)
    > >> For intLoop = Asc("0") To Asc("9")
    > >> If strChar = Chr(intLoop) Then
    > >> blnStrip = False
    > >> Exit For
    > >> End If
    > >> Next intLoop
    > >> If blnStrip = False Then strTemp = strChar & strTemp
    > >> Next lngLoop
    > >> StripAllNonNumericChars = strTemp
    > >> Exit Function
    > >> End Function
    > >>
    > >>
    > >> --
    > >>
    > >> Ken Snell
    > >> <MS ACCESS MVP>
    > >>
    > >> "tom" <Spamblocker@ameritech.net> 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.
    > >>>
    > >>
    > >>

    > >
    > >

    >
    >
     
  14. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
    data type? The function is returning a string data type, which I did so that
    you are not limited in the length of the "numeric" string that you use.

    Assuming that the number of digits in the purchase order number will fit
    into a Long Integer value, change Query2 to this:

    SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
    invoice.[Invoice Number]
    FROM invoice;
    --

    Ken Snell
    <MS ACCESS MVP>

    "Tom" <TOM@spam.net> wrote in message
    news:sIydneWD2NdoWQHZnZ2dnUVZ_oudnZ2d@comcast.com...
    > SELECT
    > FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order
    > Number] = Query2.Expr1;
    >
    > Query2 is as follows:
    >
    > SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
    > invoice.[Invoice Number]
    > FROM invoice;
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:#Wh4hCxlGHA.4196@TK2MSFTNGP03.phx.gbl...
    >> ?? Using the function in a join in a query? Post the SQL statement that
    >> you
    >> created to do this so I can see what you mean.
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >> "tom" <Spamblocker@ameritech.net> wrote in message
    >> news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    >> > Your code works but when I try to use it in a join
    >> > with a another table with text field, I am getting
    >> > a data type mismatch. Some of the entries are coming
    >> > up #error. Could that be the problem?
    >> >
    >> >
    >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    >> > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    >> >> Here is a function that will do this:
    >> >>
    >> >> '********************************
    >> >> '* *
    >> >> '* Fxn StripAllNonNumericChars *
    >> >> '* *
    >> >> '********************************
    >> >>
    >> >> ' ** This function strips all nonnumeric characters from a text
    >> >> string.
    >> >>
    >> >> Function StripAllNonNumericChars(strOriginalString As String) As
    >> >> String
    >> >> Dim blnStrip As Boolean
    >> >> Dim intLoop As Integer
    >> >> Dim lngLoop As Long
    >> >> Dim strTemp As String, strChar As String
    >> >> On Error Resume Next
    >> >> For lngLoop = Len(strOriginalString) To 1 Step -1
    >> >> blnStrip = True
    >> >> strChar = Mid(strOriginalString, lngLoop, 1)
    >> >> For intLoop = Asc("0") To Asc("9")
    >> >> If strChar = Chr(intLoop) Then
    >> >> blnStrip = False
    >> >> Exit For
    >> >> End If
    >> >> Next intLoop
    >> >> If blnStrip = False Then strTemp = strChar & strTemp
    >> >> Next lngLoop
    >> >> StripAllNonNumericChars = strTemp
    >> >> Exit Function
    >> >> End Function
    >> >>
    >> >>
    >> >> --
    >> >>
    >> >> Ken Snell
    >> >> <MS ACCESS MVP>
    >> >>
    >> >> "tom" <Spamblocker@ameritech.net> 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.
    >> >>>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  15. Tom

    Tom
    Expand Collapse
    Guest

    purchase order.Purchase order number is a text type. Your query
    is returning some values #error

    I have not yet tried to strip out these and try the
    query again. It seems that your code should
    return a string, which I understand would be a
    text type, so I am a bit confused.


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message news:uXwy0E6lGHA.1552@TK2MSFTNGP04.phx.gbl...
    > I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
    > data type? The function is returning a string data type, which I did so that
    > you are not limited in the length of the "numeric" string that you use.
    >
    > Assuming that the number of digits in the purchase order number will fit
    > into a Long Integer value, change Query2 to this:
    >
    > SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
    > invoice.[Invoice Number]
    > FROM invoice;
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "Tom" <TOM@spam.net> wrote in message
    > news:sIydneWD2NdoWQHZnZ2dnUVZ_oudnZ2d@comcast.com...
    > > SELECT
    > > FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order
    > > Number] = Query2.Expr1;
    > >
    > > Query2 is as follows:
    > >
    > > SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
    > > invoice.[Invoice Number]
    > > FROM invoice;
    > >
    > >
    > > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > > news:#Wh4hCxlGHA.4196@TK2MSFTNGP03.phx.gbl...
    > >> ?? Using the function in a join in a query? Post the SQL statement that
    > >> you
    > >> created to do this so I can see what you mean.
    > >>
    > >> --
    > >>
    > >> Ken Snell
    > >> <MS ACCESS MVP>
    > >>
    > >> "tom" <Spamblocker@ameritech.net> wrote in message
    > >> news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    > >> > Your code works but when I try to use it in a join
    > >> > with a another table with text field, I am getting
    > >> > a data type mismatch. Some of the entries are coming
    > >> > up #error. Could that be the problem?
    > >> >
    > >> >
    > >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > >> > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    > >> >> Here is a function that will do this:
    > >> >>
    > >> >> '********************************
    > >> >> '* *
    > >> >> '* Fxn StripAllNonNumericChars *
    > >> >> '* *
    > >> >> '********************************
    > >> >>
    > >> >> ' ** This function strips all nonnumeric characters from a text
    > >> >> string.
    > >> >>
    > >> >> Function StripAllNonNumericChars(strOriginalString As String) As
    > >> >> String
    > >> >> Dim blnStrip As Boolean
    > >> >> Dim intLoop As Integer
    > >> >> Dim lngLoop As Long
    > >> >> Dim strTemp As String, strChar As String
    > >> >> On Error Resume Next
    > >> >> For lngLoop = Len(strOriginalString) To 1 Step -1
    > >> >> blnStrip = True
    > >> >> strChar = Mid(strOriginalString, lngLoop, 1)
    > >> >> For intLoop = Asc("0") To Asc("9")
    > >> >> If strChar = Chr(intLoop) Then
    > >> >> blnStrip = False
    > >> >> Exit For
    > >> >> End If
    > >> >> Next intLoop
    > >> >> If blnStrip = False Then strTemp = strChar & strTemp
    > >> >> Next lngLoop
    > >> >> StripAllNonNumericChars = strTemp
    > >> >> Exit Function
    > >> >> End Function
    > >> >>
    > >> >>
    > >> >> --
    > >> >>
    > >> >> Ken Snell
    > >> >> <MS ACCESS MVP>
    > >> >>
    > >> >> "tom" <Spamblocker@ameritech.net> 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.
    > >> >>>
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  16. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Let's see some examples of data that are in "Source of Order" field .. by
    any chance, can some of them be Null values? Assuming that they might be,
    change the function to this so that it handles Null value being passed to
    the function:

    Function StripAllNonNumericChars(varOriginalString As Variant) As String
    Dim blnStrip As Boolean
    Dim intLoop As Integer
    Dim lngLoop As Long
    Dim strTemp As String, strChar As String
    Dim strOriginalString As String
    On Error Resume Next
    strTemp = ""
    strOriginalString = Nz(varOriginalString, "")
    For lngLoop = Len(strOriginalString) To 1 Step -1
    blnStrip = True
    strChar = Mid(strOriginalString, lngLoop, 1)
    For intLoop = Asc("0") To Asc("9")
    If strChar = Chr(intLoop) Then
    blnStrip = False
    Exit For
    End If
    Next intLoop
    If blnStrip = False Then strTemp = strChar & strTemp
    Next lngLoop
    StripAllNonNumericChars = strTemp
    Exit Function
    End Function


    --

    Ken Snell
    <MS ACCESS MVP>

    "Tom" <TOM@spam.net> wrote in message
    news:3aqdnaogmeJy4QDZnZ2dnUVZ_o2dnZ2d@comcast.com...
    > purchase order.Purchase order number is a text type. Your query
    > is returning some values #error
    >
    > I have not yet tried to strip out these and try the
    > query again. It seems that your code should
    > return a string, which I understand would be a
    > text type, so I am a bit confused.
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:uXwy0E6lGHA.1552@TK2MSFTNGP04.phx.gbl...
    >> I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
    >> data type? The function is returning a string data type, which I did so
    >> that
    >> you are not limited in the length of the "numeric" string that you use.
    >>
    >> Assuming that the number of digits in the purchase order number will fit
    >> into a Long Integer value, change Query2 to this:
    >>
    >> SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
    >> invoice.[Invoice Number]
    >> FROM invoice;
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >> "Tom" <TOM@spam.net> wrote in message
    >> news:sIydneWD2NdoWQHZnZ2dnUVZ_oudnZ2d@comcast.com...
    >> > SELECT
    >> > FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase
    >> > Order
    >> > Number] = Query2.Expr1;
    >> >
    >> > Query2 is as follows:
    >> >
    >> > SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
    >> > invoice.[Invoice Number]
    >> > FROM invoice;
    >> >
    >> >
    >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    >> > news:#Wh4hCxlGHA.4196@TK2MSFTNGP03.phx.gbl...
    >> >> ?? Using the function in a join in a query? Post the SQL statement
    >> >> that
    >> >> you
    >> >> created to do this so I can see what you mean.
    >> >>
    >> >> --
    >> >>
    >> >> Ken Snell
    >> >> <MS ACCESS MVP>
    >> >>
    >> >> "tom" <Spamblocker@ameritech.net> wrote in message
    >> >> news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    >> >> > Your code works but when I try to use it in a join
    >> >> > with a another table with text field, I am getting
    >> >> > a data type mismatch. Some of the entries are coming
    >> >> > up #error. Could that be the problem?
    >> >> >
    >> >> >
    >> >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in
    >> >> > message
    >> >> > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    >> >> >> Here is a function that will do this:
    >> >> >>
    >> >> >> '********************************
    >> >> >> '* *
    >> >> >> '* Fxn StripAllNonNumericChars *
    >> >> >> '* *
    >> >> >> '********************************
    >> >> >>
    >> >> >> ' ** This function strips all nonnumeric characters from a text
    >> >> >> string.
    >> >> >>
    >> >> >> Function StripAllNonNumericChars(strOriginalString As String) As
    >> >> >> String
    >> >> >> Dim blnStrip As Boolean
    >> >> >> Dim intLoop As Integer
    >> >> >> Dim lngLoop As Long
    >> >> >> Dim strTemp As String, strChar As String
    >> >> >> On Error Resume Next
    >> >> >> For lngLoop = Len(strOriginalString) To 1 Step -1
    >> >> >> blnStrip = True
    >> >> >> strChar = Mid(strOriginalString, lngLoop, 1)
    >> >> >> For intLoop = Asc("0") To Asc("9")
    >> >> >> If strChar = Chr(intLoop) Then
    >> >> >> blnStrip = False
    >> >> >> Exit For
    >> >> >> End If
    >> >> >> Next intLoop
    >> >> >> If blnStrip = False Then strTemp = strChar & strTemp
    >> >> >> Next lngLoop
    >> >> >> StripAllNonNumericChars = strTemp
    >> >> >> Exit Function
    >> >> >> End Function
    >> >> >>
    >> >> >>
    >> >> >> --
    >> >> >>
    >> >> >> Ken Snell
    >> >> >> <MS ACCESS MVP>
    >> >> >>
    >> >> >> "tom" <Spamblocker@ameritech.net> 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.
    >> >> >>>
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  17. Graham R Seach

    Graham R Seach
    Expand Collapse
    Guest

    Ahhh, Access 97! Access 97 doesn't know about enums. Change the enums to
    constants.

    Public Const se_Char = &H1
    Public Const se_Num = &H2
    Public Const se_NonWord = &H4
    Public Const se_Space = &H8
    Public Const se_AllButChar = &H10
    Public Const se_AllButNum = &H20
    Public Const se_Custom = &H40

    Public Function StripEx(sText As String, lExpr As Integer, Optional sUsrExpr
    As String = "") As String

    Regards,
    Graham R Seach
    Microsoft Access MVP
    Canberra, Australia
    ---------------------------


    "tom" <Spamblocker@ameritech.net> wrote in message
    news:QSWmg.155912$F_3.109881@newssvr29.news.prodigy.net...
    > The problem is with the enum. I am using
    > Access 97. I am worried about upgrading for
    > fear of breaking the code I have there.
    >
    > Is there problems with upgrading from Access 97.
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:e9800OvlGHA.3576@TK2MSFTNGP04.phx.gbl...
    >> 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"
    >> 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" <Spamblocker@ameritech.net> wrote in message
    >> news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy.net...
    >>> The code wouldnt compile
    >>>
    >>>
    >>> "Graham R Seach" <gseach@accessmvp_REMOVE.com> 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" <Spamblocker@ameritech.net> 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.
    >>> >>
    >>> >
    >>> >
    >>>
    >>>

    >>
    >>

    >
    >
     
  18. Tom

    Tom
    Expand Collapse
    Guest

    That code worked. I looked at your first code
    and I think that should have worked as well.
    Access should be using an empty string by default
    IMO.

    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message news:O908zw7lGHA.2056@TK2MSFTNGP03.phx.gbl...
    > Let's see some examples of data that are in "Source of Order" field .. by
    > any chance, can some of them be Null values? Assuming that they might be,
    > change the function to this so that it handles Null value being passed to
    > the function:
    >
    > Function StripAllNonNumericChars(varOriginalString As Variant) As String
    > Dim blnStrip As Boolean
    > Dim intLoop As Integer
    > Dim lngLoop As Long
    > Dim strTemp As String, strChar As String
    > Dim strOriginalString As String
    > On Error Resume Next
    > strTemp = ""
    > strOriginalString = Nz(varOriginalString, "")
    > For lngLoop = Len(strOriginalString) To 1 Step -1
    > blnStrip = True
    > strChar = Mid(strOriginalString, lngLoop, 1)
    > For intLoop = Asc("0") To Asc("9")
    > If strChar = Chr(intLoop) Then
    > blnStrip = False
    > Exit For
    > End If
    > Next intLoop
    > If blnStrip = False Then strTemp = strChar & strTemp
    > Next lngLoop
    > StripAllNonNumericChars = strTemp
    > Exit Function
    > End Function
    >
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "Tom" <TOM@spam.net> wrote in message
    > news:3aqdnaogmeJy4QDZnZ2dnUVZ_o2dnZ2d@comcast.com...
    > > purchase order.Purchase order number is a text type. Your query
    > > is returning some values #error
    > >
    > > I have not yet tried to strip out these and try the
    > > query again. It seems that your code should
    > > return a string, which I understand would be a
    > > text type, so I am a bit confused.
    > >
    > >
    > > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > > news:uXwy0E6lGHA.1552@TK2MSFTNGP04.phx.gbl...
    > >> I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
    > >> data type? The function is returning a string data type, which I did so
    > >> that
    > >> you are not limited in the length of the "numeric" string that you use.
    > >>
    > >> Assuming that the number of digits in the purchase order number will fit
    > >> into a Long Integer value, change Query2 to this:
    > >>
    > >> SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
    > >> invoice.[Invoice Number]
    > >> FROM invoice;
    > >> --
    > >>
    > >> Ken Snell
    > >> <MS ACCESS MVP>
    > >>
    > >> "Tom" <TOM@spam.net> wrote in message
    > >> news:sIydneWD2NdoWQHZnZ2dnUVZ_oudnZ2d@comcast.com...
    > >> > SELECT
    > >> > FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase
    > >> > Order
    > >> > Number] = Query2.Expr1;
    > >> >
    > >> > Query2 is as follows:
    > >> >
    > >> > SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
    > >> > invoice.[Invoice Number]
    > >> > FROM invoice;
    > >> >
    > >> >
    > >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > >> > news:#Wh4hCxlGHA.4196@TK2MSFTNGP03.phx.gbl...
    > >> >> ?? Using the function in a join in a query? Post the SQL statement
    > >> >> that
    > >> >> you
    > >> >> created to do this so I can see what you mean.
    > >> >>
    > >> >> --
    > >> >>
    > >> >> Ken Snell
    > >> >> <MS ACCESS MVP>
    > >> >>
    > >> >> "tom" <Spamblocker@ameritech.net> wrote in message
    > >> >> news:rsWmg.155900$F_3.123494@newssvr29.news.prodigy.net...
    > >> >> > Your code works but when I try to use it in a join
    > >> >> > with a another table with text field, I am getting
    > >> >> > a data type mismatch. Some of the entries are coming
    > >> >> > up #error. Could that be the problem?
    > >> >> >
    > >> >> >
    > >> >> > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in
    > >> >> > message
    > >> >> > news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
    > >> >> >> Here is a function that will do this:
    > >> >> >>
    > >> >> >> '********************************
    > >> >> >> '* *
    > >> >> >> '* Fxn StripAllNonNumericChars *
    > >> >> >> '* *
    > >> >> >> '********************************
    > >> >> >>
    > >> >> >> ' ** This function strips all nonnumeric characters from a text
    > >> >> >> string.
    > >> >> >>
    > >> >> >> Function StripAllNonNumericChars(strOriginalString As String) As
    > >> >> >> String
    > >> >> >> Dim blnStrip As Boolean
    > >> >> >> Dim intLoop As Integer
    > >> >> >> Dim lngLoop As Long
    > >> >> >> Dim strTemp As String, strChar As String
    > >> >> >> On Error Resume Next
    > >> >> >> For lngLoop = Len(strOriginalString) To 1 Step -1
    > >> >> >> blnStrip = True
    > >> >> >> strChar = Mid(strOriginalString, lngLoop, 1)
    > >> >> >> For intLoop = Asc("0") To Asc("9")
    > >> >> >> If strChar = Chr(intLoop) Then
    > >> >> >> blnStrip = False
    > >> >> >> Exit For
    > >> >> >> End If
    > >> >> >> Next intLoop
    > >> >> >> If blnStrip = False Then strTemp = strChar & strTemp
    > >> >> >> Next lngLoop
    > >> >> >> StripAllNonNumericChars = strTemp
    > >> >> >> Exit Function
    > >> >> >> End Function
    > >> >> >>
    > >> >> >>
    > >> >> >> --
    > >> >> >>
    > >> >> >> Ken Snell
    > >> >> >> <MS ACCESS MVP>
    > >> >> >>
    > >> >> >> "tom" <Spamblocker@ameritech.net> 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.
    > >> >> >>>
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  19. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    "Tom" <TOM@spam.net> wrote in message
    news:g4ydnY7CcsdHogLZnZ2dnUVZ_tidnZ2d@comcast.com...
    > That code worked. I looked at your first code
    > and I think that should have worked as well.
    > Access should be using an empty string by default
    > IMO.
    >


    Actually, there is a good reason for having Null and empty string as
    potential values. Null means that there is no value for that field; empty
    string means that the value is a blank (there is a value, but it contains no
    characters). These are distinctly different values with good uses.

    You can change the design of your database so that the default value of the
    text field is an empty string and so that the field is required to have a
    value. That will eliminate the possibility of Null values in the records in
    the future. Before you do this, though, run an update query to change all
    Null values in that field to an empty string.
    --

    Ken Snell
    <MS ACCESS MVP>
     

Share This Page