469,907 Members | 2,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,907 developers. It's quick & easy.

Easiest way to display properly formatted US 10 and 7 digit phone numbers?

MLH
Using A97, I have tables with 10-char text phone number fields to
allow entry of xxxyyyyyyy phone numbers - where xxx is the 3-digit
area code and yyyyyyy is the 7-digit phone number. I wish to display
these as (xxx) yyy-yyyy. Trouble is, some telephone numbers are
entered by users as only 7-digits. They omit the area code in many
cases, understandably. They consider it redundant and its not
necessary for them to dial it - in most cases, only the 7-digit
yyyyyyy part is required.

If I'm displaying them in query, what's the best syntax to put in
the SQL in the field displaying MyTable.MyPhoneNum? If
there's a 10-digit number, I wanna see (xxx) yyy-yyyy and if
there's only 7-digits, I wanna see yyy-yyyy.
Nov 13 '05 #1
2 7787
MLH
Part 2 of this question is how to set up the Format property for a
textbox control on a data entry form who's record source is a table to
display the phone number properly formatted as a 10-digit or 7-digit
phone number after user enters a number into the field.

I don't want to use an input mask like !\(999") "000\-0000;;_ or like
!\(000") "000\-0000;;_ because I do not want to restrict the user
to entering 10 digits or force him to arrow past the first three (AC)
digits if he wishes to enter 7-digits only (instead of 10). In other
words, I want to give the user complete freedom to enter 7 or 10
at his own discretion. And after he enters it into the control, I want
to show it properly formatted. Is this making sense?
Nov 13 '05 #2
On Tue, 16 Aug 2005 22:31:42 -0400, MLH wrote:
Using A97, I have tables with 10-char text phone number fields to
allow entry of xxxyyyyyyy phone numbers - where xxx is the 3-digit
area code and yyyyyyy is the 7-digit phone number. I wish to display
these as (xxx) yyy-yyyy. Trouble is, some telephone numbers are
entered by users as only 7-digits. They omit the area code in many
cases, understandably. They consider it redundant and its not
necessary for them to dial it - in most cases, only the 7-digit
yyyyyyy part is required.

If I'm displaying them in query, what's the best syntax to put in
the SQL in the field displaying MyTable.MyPhoneNum? If
there's a 10-digit number, I wanna see (xxx) yyy-yyyy and if
there's only 7-digits, I wanna see yyy-yyyy.


PhoneNum:IIf(Len([PhoneField])=10,Format([PhoneField],"(@@@)
@@@-@@@@"),Format([PhoneField],"@@@-@@@@"))

(805) 123-4567
123-4567

Or if you don't mind an empty area code section if the length is 7
characters, i.e. ( ) 456-1478
PhoneNum: Format([Phone],"(@@@) @@@-@@@@")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by joemono | last post: by
reply views Thread by Eben Goodman | last post: by
4 posts views Thread by Brian Henry | last post: by
8 posts views Thread by nineteen | last post: by
14 posts views Thread by thehobbit | last post: by
7 posts views Thread by Propoflady | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.