[AccessD] Phone Number with optional extension

    You are most welcome Rocky!

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Rocky Smolin 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, May 29, 2012 17:59
  Subject: Re: [AccessD] Phone Number with optional extension


  A.D.:

  You've done it again!  Copy, paste, viola!  Phone number perfectly
  formatted.  

  Thanks so much.

  Best,

  Rocky Smolin
  Beach Access Software
  858-259-4334
  www.bchacc.com
  www.e-z-mrp.com
  Skype: rocky.smolin 


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
  Sent: Tuesday, May 29, 2012 1:21 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Phone Number with optional extension

  Rocky,

      Sample function Fn_PhoneNum() as given below, would first clean up the
  existing phone number by removing non-numeric characters (if any), followed
  by building up a dynamic format string, returning the phone number (with
  extension - if any) duly formatted.

  ' Sample function in general module
  '============================
  Function Fn_PhoneNum( _
                  PhoneNum As String) As String
      ' Removes all non-numeric characters
      Dim Txt1 As String, Txt2 As String
      Dim Cnt As Long
      Dim Fmt As String, Extn As Long
      
      ' Remove non-numeric characters if any
      Txt2 = ""
      For Cnt = 1 To Len(PhoneNum)
          Txt1 = Mid(PhoneNum, Cnt, 1)
          If IsNumeric(Txt1) Then
              Txt2 = Txt2 & Txt1
          End If
      Next
      
      ' Build dynamic format string
      Fmt = "@@@-@@@-@@@@"
      Extn = Len(Txt2) - 10
      If Extn > 0 Then
          Fmt = Fmt & "-" & String(Extn, "@")
      End If
      
      Fn_PhoneNum = Format(Txt2, Fmt)
  End Function
  '==========================

      For example, let us say that variable PNum holds the phone number.
  Calling Fn_PhoneNum(PNnum) will return correctly formatted output, depending
  upon the presence or absence of any digits beyond the normal 10. The extent
  of extension digits can be variable and there is no restriction on their
  number.

      Typical results could be:
      402-476-8482
      402-476-8482-12
      402-476-8482-12345

  Best wishes,
  A.D. Tejpal
  ------------