Tags: 10-char, 3-digitarea, a97, access, code, database, digit, display, easiest, entry, fields, formatted, microsoft, mysql, number, numbers, oracle, phone, properly, sql, tables, text, toallow, xxx, xxxyyyyyyy

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

On Database » Microsoft Access

2,822 words with 2 Comments; publish: Mon, 31 Dec 2007 11:52:00 GMT; (25078.13, « »)

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.

All Comments

Leave a comment...

  • 2 Comments
    • 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?

      #1; Mon, 31 Dec 2007 11:53:00 GMT
    • 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],"(.ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.)

      .ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.-.ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com."),Format([PhoneField],".ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.-.ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com."))

      (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],"(.ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.) .ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.-.ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com..ms-access.todaysummary.com.")

      --

      Fred

      Please only reply to this newsgroup.

      I do not reply to personal email.

      #2; Mon, 31 Dec 2007 11:54:00 GMT