Update Format for Phone Numbers (2000)

Topic

New Reply

I have a phone field which contains ten digits, no punctuation. I wanted to end up with phone numbers in a more readable format, i.e. 729-1234 instead of 2077291234. Because someone else uses this data, I cannot change the ten digit numbers in the telephone field.
I accomplished my goal by:
Creating a new field in the table called “Phone”
Creating an update query using the Right function to pull the the seven digits on the right side of the phone number in the Telephone field and put it into the new Phone field.
Creating a second query which updates the Phone field to add the dash between the third and fourth digits using the following formula:
Left([Phone],3) & “-” & Right([Phone],4)

My question is: Could I have gotten from my ten digit Telephone to my seven digit plus dash Phone in a single step instead of two separate queries, and if so, how? I seem to run into these kind of needs often, and would like to understand better how to handle them.
Thank you,
-cynthia

Reply | Quote

Replies

I solved it! While my initial search had not brought up anything relevant, I was just browsing a post about Dates and was able to extract from there a function that will do it.
Val(Mid([Telephone],4,3)) & “-” & Val(Mid([Telephone],7,4))

So now, so I can understand it (I took the text from the Date solution and did trials and errors with it until it did the right thing to my phone field), I understand – through trial and error – what the digits in the function are doing, and I knew how to add the text in, but can someone just explain to me what “Val”, and “Mid” are?

Thanks,
-cynthia

Reply | Quote

Val() gives you the numerical value of some text field, if any can be derived.

Mid() lets you get text from the middle if a string value.

HTH,

Tom

Reply | Quote

Thank you.
I’m getting it slowly but surely.

Reply | Quote

To format 10 digit phone number as 7 digit phone number w/o area code & with hyphen, you can also use this expression:

 Format(Right([Phone],7),"&&&-&&&&")

“Phone” is name of the field with 10-digit phone number. Right function returns 7 digits from right while Format function adds hyphen, the “&” symbol is a character placeholder for custom string format (if expression being evaluated has character in position, character is displayed, else nothing is displayed).

HTH

Reply | Quote

I must have done something wrong the first time. It works now!
Thanks Mark and Charlotte.
-c

Reply | Quote

I don’t understand why you need to use the Val function here, the Mid’s should be enough.
Pat

Reply | Quote

*Don’t* use Val on things like phone numbers unless you really want to lose leading zeros.

Reply | Quote

Thank you Charlotte. It had, in fact, stripped the leading zeros off the group of four digits on the left (the database has 24,000 records, so I hadn’t noticed it at first). Removing the Val from my code seems to have everything working just dandy. I couldn’t get Mark’s suggestion to work in a query form – though I suspect that it would work just fine in an event procedure.

-cynthia

Reply | Quote

It works in a query for me in either Access 2000 or Access XP. You did remember to change [Phone] to the correct field name for your table, didn’t you?

Reply | Quote

Thank you for all the extra tips.
I used “Val” only because I don’t really fully know what I’m doing here ;-), so I was copying and changing an example someone had given on another post for a date. It worked, so I left it there. The additional tips Charlotte, Tom, Pat and Mark have posted are helping me understand better what was actually happening there – and how to handle future situations. Thanks.
-cynthia

Reply | Quote