[AccessD] Returning only numbers from a phone number field

Gustav,

My solution was much shorter but yours is much more thorough, I'm going to
keep this one around.

Thanks!

Rusty

-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk]
Sent: Thursday, September 06, 2007 12:13 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Returning only numbers from a phone number field


Hi Rusty

You may use my little function:

Public Function TrimNumString( _
  ByVal strNumString As String, _
  Optional ByVal strDecimalChr As String, _
  Optional ByVal booAcceptMinus As Boolean) _
  As String

' Removes any non-numeric character from strNumString including hexadecimal
characters.
' If strDecimalChr is specified, first occurrence of this is not removed.
' If booAcceptMinus is True, a leading or trailing minus sign is accepted.
'
' 1999-08-27. Cactus Data ApS, CPH.
' 2001-06-21. Speed optimized for large string (64 K).
' 2003-12-10. intOffset changed to lngOffset.
  
  Const cbytNeg   As Byte = 45  ' "-"
  
  Dim lngPos      As Long
  Dim lngLen      As Long
  Dim lngOffset   As Long
  Dim booDec      As Boolean
  Dim booNeg      As Boolean
  Dim bytChr      As Byte
  Dim bytDec      As Byte
  Dim strNum      As String
  
  strNumString = Trim(strNumString)
  lngLen = Len(strNumString)
  If lngLen > 0 Then
    If Len(strDecimalChr) > 0 Then
      bytDec = Asc(strDecimalChr)
    End If
    ' Create empty result string of maximum possible length.
    strNum = Space(lngLen)
    
    For lngPos = 1 To lngLen
      bytChr = Asc(Mid(strNumString, lngPos, 1))
      Select Case bytChr
        Case 48 To 57
          ' Digit.
        Case bytDec
          ' Decimal point.
          If booDec = False Then
            ' One decimal point only.
            booDec = True
          End If
        Case cbytNeg
          ' Minus sign.
          bytChr = 0
          If booAcceptMinus = True And booNeg = False Then
            If Len(Trim(strNum)) = 0 Or lngPos = lngLen Then
              bytChr = cbytNeg
              ' One minus sign only.
              booNeg = True
            End If
          End If
        Case Else
          ' Ignore any other character.
          bytChr = 0
      End Select
      If bytChr > 0 Then
        ' Append accepted character by inserting it in result string.
        lngOffset = lngOffset + 1
        Mid(strNum, lngOffset) = Chr(bytChr)
      End If
    Next
  End If
  
  ' Trim and return result string.
  TrimNumString = Left(strNum, lngOffset)

End Function

/gustav

>>> rusty.hammond at cpiqpc.com 06-09-2007 19:09 >>>
Is there a built in function that would return only the numbers from a text
field?  I have a table that allows the users to enter a phone number however
they like, ie with or without parentheses or dashes or dots or any
combination of them.

I would like to extract only the numbers (0-9) from this field.  Is there
anything built in to Access 2003 to do this or do I just need to create my
own little function to do it?

TIA, 

Rusty Hammond


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************