ACCESS FEED: Re: [MS_AccessPros] Phone number formatting

> I think I am going to have to save this one for a day when it is
> very cold out

Bill - Would that be when Hell freezes over? [grin]

Bill Mosca

--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> Duane,
>
> Thanks. I think I am going to have to save this one for a day when it is
> very cold out and I have some time.
>
>
>
> Have a great day,
>
> Bill
>
>
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
> Sent: Monday, September 17, 2012 11:57 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Phone number formatting
>
>
>
>
>
> I would store the phone numbers as text in a specific format. You could use
> code in the afterupdate of a text box like:
>
> Private Sub txtHomePhone_AfterUpdate()
> Me.txtHomePhone = FixPhone(Me.txtHomePhone)
> End Sub
>
> The function would be something like:
>
> Public Function FixPhone(varPhone As Variant, Optional strFormat As String =
> "(###) ###-####") As Variant
> Dim strOut As String
> Dim strCharacter As String
> Dim intI As Integer
>
> If Not IsNull(varPhone) Then
>
> 'get rid of all non-numeric
> For intI = 1 To Len(varPhone)
> strCharacter = Mid(varPhone, intI, 1)
> If InStr(1, "0123456789", strCharacter) > 0 Then
> strOut = strOut & strCharacter
> End If
> Next
> 'you can add error management for crappy phone numbers
> 'add back in the ()s etc
> If Len(strOut) <= 7 Then
> FixPhone = Format(strOut, Right(strFormat, 8))
> Else
> FixPhone = Format(strOut, strFormat)
> End If
> End If
> End Function
>
> Duane Hookom
> MS Access MVP
>
>
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > From: Bill.Singer@... <mailto:Bill.Singer%40at-group.net>
> > Date: Mon, 17 Sep 2012 11:08:38 -0500
> > Subject: RE: [MS_AccessPros] Phone number formatting
> >
> > John,
> >
> >
> >
> > Well originally I was not but now that you bring it up.
> >
> >
> >
> > My area codes are
> >
> > 763
> >
> > 651
> >
> >
> >
> > I really should not be getting number outside of that range unless someone
> > has moved in and has a cell phone from out of the area.
> >
> >
> >
> > What would you propose?
> >
> >
> >
> >
> >
> > Bill Singer
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Marshall
> > Sent: Monday, September 17, 2012 11:00 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] Phone number formatting
> >
> >
> >
> >
> >
> > So you will be flagging anything that does not use one of their seven area
> > codes?
> >
> > John.
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> > Singer
> > Sent: Monday, September 17, 2012 11:46 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] Phone number formatting
> >
> > Good point.
> >
> > All numbers will be for Minnesota, but I will still look them over.
> >
> > Bill Singer
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> > Marshall
> > Sent: Monday, September 17, 2012 10:24 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] Phone number formatting
> >
> > I would do a sanity check afterwards to make sure that the result IS ten
> > digits long. They could contain missing area codes or not be a North
> > American based phone number.
> >
> > John.
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Mosca
> > Sent: Monday, September 17, 2012 10:32 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Phone number formatting
> >
> > Bill
> >
> > It's easy-squeezy to remove all the extra characters. Run an update on the
> > table and update the phone number like this:
> >
> > UPDATE MyTable
> > SET PhoneNum = Replace(Replace(Replace([PhoneNum],"(",""),")",""),"-","")
> >
> > The above gets rid of "(", ")" and "-" by replacing them with an empty
> > string. The multiple Replace() functions do it all in one pass.
> >
> > One kind of char at a time would be:
> > Replace([PhoneNum],"(","")
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> > <Bill.Singer@> wrote:
> > >
> > > OK Bill and Duane. I am convince I need to remove my formatting in the
> > > table. I will do that. So the form that I use to view and input the
> phone
> > > numbers will have a format the actual table will not and if I view the
> > phone
> > > number in the table it will look like 1234567890 but the form will show
> > > 123-456-7890. Am I understanding that correctly? That sounds fine to me.
> > > The problem I have is how do I get the 700+ phone numbers changed. Do I
> > > have to retype all of them. Duane. What is this code to update all the
> > > current phone numbers in the database? Will it go through the phone
> > > numbers and remove the dashes?
> > >
> > >
> > >
> > > Bill Singer
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane
> > Hookom
> > > Sent: Sunday, September 16, 2012 9:02 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: RE: [MS_AccessPros] Phone number formatting
> > >
> > >
> > >
> > >
> > >
> > > I agree with Bill (Mosca). If this was my application, I would create a
> > > small function to update all the phone numbers to store the numbers with
> > the
> > > correct formatting.
> > >
> > > Then get rid of all input masks and ensure the appropriate format with
> > code
> > > or other method.
> > >
> > > Duane Hookom
> > > MS Access MVP
> > >
> > >
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > From: wrmosca@ <mailto:wrmosca%40comcast.net>
> > > > Date: Sun, 16 Sep 2012 18:52:05 -0700
> > > > Subject: RE: [MS_AccessPros] Phone number formatting
> > > >
> > > > Bill
> > > >
> > > >
> > > >
> > > > The first rule is to not store phone number (or any other data) in a
> > > specific
> > > > format. They should be stored as text. Then apply the formatting to
> the
> > > forms
> > > > that display the data.
> > > >
> > > >
> > > >
> > > > Capeesh?
> > > >
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Bill
> > > >
> > > >
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > agent1of6
> > > > Sent: Sunday, September 16, 2012 3:12 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: [MS_AccessPros] Phone number formating
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > I am putting together a form that will look up clients based on a few
> > > items. One
> > > > of the items is Home Phone number. I created a combo box that searches
> > the
> > > table
> > > > t_Clients.
> > > >
> > > > In the table I have the input mask as phone number.
> > > >
> > > > the problem is that I am getting phone numbers showing up in the combo
> > box
> > > with
> > > > the dashes in different places. I am getting these formats...
> > > >
> > > > 123-456-7890
> > > > 123456-7890
> > > > 123-4567890
> > > >
> > > > Any idea what I have to do to get all the phone numbers in the combo
> box
> > > to show
> > > > up the same way. The search function of the combo box uses the dash as
> > > part of
> > > > its sorting and could cause the end user to miss the number if the
> dash
> > is
> > > put,
> > > > or not put in.
> > > >
> > > > Thanks,
> > > > Bill
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > [Non-text portions of this message have been removed]
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>