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@at-group.net <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]
Tidak ada komentar:
Posting Komentar