Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2006
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search string for a number (Access 2003)

    I need to be able to determine if any of the characters in a string is numerical. For example, if "Jack1Hello" contains the numbers 0 to 9? Is there a simple way to do this? I have written a little function which works (see below), but I was wondering if there is already a function to do it.

    Public Function f_ContainsNumbers(sText As String) As Boolean
    On Error GoTo err_Handler

    If IsNull(sText) Then Exit Function

    If InStr(1, sText, "0") > 0 Or _
    InStr(1, sText, "1") > 0 Or _
    InStr(1, sText, "2") > 0 Or _
    InStr(1, sText, "3") > 0 Or _
    InStr(1, sText, "4") > 0 Or _
    InStr(1, sText, "5") > 0 Or _
    InStr(1, sText, "6") > 0 Or _
    InStr(1, sText, "7") > 0 Or _
    InStr(1, sText, "8") > 0 Or _
    InStr(1, sText, "9") > 0 Then
    f_ContainsNumbers = True
    End If
    Exit Function

    err_Handler:
    MsgBox "Error!", vbCritical, "f_ContainsNumbers"

    End Function

  2. #2
    Star Lounger
    Join Date
    Jul 2006
    Location
    Colorado, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search string for a number (Access 2003)

    I don't know of a function like it. IsNumeric would not flag it, but a more compact test would use Like instead of Instr.

    Public Function f_ContainsNumbers(sText As String) As Boolean
    On Error GoTo err_Handler

    If IsNull(sText) Then Exit Function

    if sText like "*[0-9]*" then
    f_ContainsNumbers = True

    End If
    Exit Function

    err_Handler:
    MsgBox "Error!", vbCritical, "f_ContainsNumbers"

    End Function

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search string for a number (Access 2003)

    Or slightly shorter

    Public Function f_ContainsNumbers(sText As String) As Boolean
    On Error GoTo err_Handler
    If IsNull(sText) Then Exit Function
    f_ContainsNumbers = (sText like "*[0-9]*")
    Exit Function

    err_Handler:
    MsgBox "Error!", vbCritical, "f_ContainsNumbers"
    End Function

  4. #4
    Lounger
    Join Date
    Aug 2006
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search string for a number (Access 2003)

    How can I extend this function to return the value of the number? For example, if the text string is "PO Box 123", I would like it to return 123?

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search string for a number (Access 2003)

    Can the number be anywhere in the string, or will it always be at the end?
    Is there only one number in the string, or can it contain several, for example "234 5th Street, Berkeley, CA 94705"? If the latter, which of the numbers would you like to return?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search string for a number (Access 2003)

    >On Error GoTo err_Handler

    I'm curious; what type of errors do you anticipate?

Posting Permissions

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