A.D. Tejpal
adtp at airtelmail.in
Tue May 29 03:21:02 CDT 2012
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
------------
----- Original Message -----
From: Rocky Smolin
To: 'Access Developers discussion and problem solving'
Sent: Tuesday, May 29, 2012 10:35
Subject: Re: [AccessD] Phone Number with optional extension
For the phone number 402-476-8482
that gives me (999) 999-9999 4024768482.
Replacing the 9s with 0s gives (040) 247-6848 2
To make matters worse, data converted earlier from a spreadsheet has parens
and dashes, the newly entered data does not.
Looks like I'll have to 1) clean up the current data, 2) parse the phone
number using left and mid string, unless I can find some magic mask that
will work.
Tks.
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Monday, May 28, 2012 9:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Phone Number with optional extension
I suppose you could try Format$([telephonefield], "(999) 999-9999 #").
That assumes you always have the telephone number and you may have a
numeric extension.
Charlotte Foust
On Mon, May 28, 2012 at 8:25 PM, Rocky Smolin <rockysmolin at bchacc.com>wrote:
> Code. I was hoping for a format string. But if I hafta...I'll parse.
> :(
>
> R
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
> Foust
> Sent: Monday, May 28, 2012 4:38 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Phone Number with optional extension
>
> OMG, deliver me from legacy systesm! If you always have an area code,
> just parse out the rest of the string with the Mid function then and
> apply the formatting to that. Are you trying to do this in code or in
> the contolsource of the report control?
>
> Charlotte Foust
>
> On Mon, May 28, 2012 at 4:33 PM, Rocky Smolin <rockysmolin at bchacc.com
> >wrote:
>
> > That would be too easy. :)
> >
> > The extension is right there in the text field with the phone number.
> > (Legacy system...)
> >
> > R
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
> > Foust
> > Sent: Monday, May 28, 2012 3:07 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Phone Number with optional extension
> >
> > So is the extension in its own field? You can try:
> >
> > =format$([telephonefield], "(999) 999-9999") & " "
> > & IIF(Len(Trim([extensionfield]))>0,format$([extensionfield], "#"),
> > "")
> >
> > Maybe I'm not understanding the problem.
> >
> > Charlotte Foust
> > On Mon, May 28, 2012 at 12:05 PM, Rocky Smolin
> > <rockysmolin at bchacc.com>wrote:
> >
> > > I'm creating a sentence in code like
> > >
> > > "Please call Joe blow at: " & (then add the phone number here) & "
> > > for more information about the class."
> > >
> > > The phone number is standard 3-3-4 but may or may not have an
> > > extension of
> > > 1
> > > to 5 numbers.
> > >
> > > In the database it's a mix of formatted (with parens and hyphen)
> > > and unformatted and the phone number on the form is OK. But that
> > > input mask doesn't work on the output side.
> > >
> > > R
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > > Charlotte Foust
> > > Sent: Monday, May 28, 2012 11:31 AM
> > > To: Access Developers discussion and problem solving
> > > Subject: Re: [AccessD] Phone Number with optional extension
> > >
> > > You mean you want to format it in code and insert it after the
> > > phone number?
> > > What format are you trying to use and where is the extension
> > > coming
> from?
> > > Is it in a separate field or captured as part of the phone number
> > > with an input mask?
> > >
> > > Charlotte Foust
> > >
> > > On Mon, May 28, 2012 at 11:09 AM, Rocky Smolin
> > > <rockysmolin at bchacc.com>wrote:
> > >
> > > > Dear List:
> > > >
> > > > I need to print a phone number in a report that has an optional
> > > > extension of up to five characters and can't seem to get the
> > > > right mask. I'm inserting the phone number thought code in the
> > > > Detail Format event as it's part of other text in a letter.
> > > >
> > > > Does anyone know how to do this?
> > > >
> > > > MTIA
> > > >
> > > > Rocky