Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Cool Unanswered: String Manipulation

    Can anyone tell me if there is a function to return only the numerica characters from the followin string(s):

    ABC1234
    AB45DEF
    A43D34


    I want to return only the numerical characters, is there a function in MsAccess to do thi? i have tried using the Instrrev function, but that only seems to work for characters.

    Many thanks

    Tracey

  2. #2
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Angry Re: String Manipulation

    I don't thing so...
    You may build a Function with VBA that search the string for numeric characters.

    Paulo Gonçalves

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the access replace function can do it, in access 2000 and up, i think (i cannot check it because i only have access 97)

    select eval(replace(yourfield,'?',''))
    from yourtable

    this should be in the help file (he said, hopefully)


    rudy

  4. #4
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    The replace function only allows to replace from one character up to an entire expression. Doesn't support to replace the charecters from "a" to "z".
    As i told you you can build a function in VBA to do this and inside that function you will use the replace function.

    ______________
    Paulo Gonçalves

  5. #5
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Try this function.

    Public Function StrToNumber(StringIn As String) As Variant
    ' Accepts a string and returns any numbers

    On Error GoTo ErrorProcedure

    Dim posn As Integer ' char position in string
    Dim str As String ' holds numbers as we find them

    For posn = 1 To Len(StringIn)
    If IsNumeric(Mid(StringIn, posn, 1)) Then
    str = str & Mid(StringIn, posn, 1)
    End If
    Next posn

    If Len(str) > 0 Then
    'Only for testing
    Debug.Print str
    StrToNumber = str
    'Only for testing
    Debug.Print StrToNumber
    End If

    ExitProcedure:
    Exit Function
    ErrorProcedure:
    MsgBox "Error in function." & Err.number & Err.Description
    Resume ErrorProcedure
    End Function


    David

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my understanding is that you can use vb functions in access

    see About using wildcard characters to search for partial or matching values

    that page says you can use wildcards in Replace

    thus ... eval(replace(yourfield,'?','')) will strip out the alphabetic characters (the question mark is a wildcard for alphabetics) and evaluate the result as a number

    and i'm pretty sure you can use Replace in access 2000, but i do not have it myself, so i must leave it to someone with access 2000 to confirm

    rudy

  7. #7
    Join Date
    Feb 2003
    Posts
    2
    Thanks, this worked a treat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •