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@yahoogroups.com] On Behalf Of John Marshall
Sent: Monday, September 17, 2012 11:00 AM
To: MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
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@yahoogroups.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>
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@yahoogroups.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>
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> , "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@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 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>
> 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>
> > 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@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
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>
> > 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]
Tidak ada komentar:
Posting Komentar