Thread: Update Format for Phone Numbers (2000)

1. Update Format for Phone Numbers (2000)

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

2. Re: Update Format for Phone Numbers (2000)

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

3. Re: Update Format for Phone Numbers (2000)

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

4. Re: Update Format for Phone Numbers (2000)

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

5. Re: Update Format for Phone Numbers (2000)

I don't understand why you need to use the Val function here, the Mid's should be enough.
Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

6. Re: Update Format for Phone Numbers (2000)

To format 10 digit phone number as 7 digit phone number w/o area code & with hyphen, you can also use this expression:
<pre> Format(Right([Phone],7),"&&&-&&&&")</pre>

"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

7. Re: Update Format for Phone Numbers (2000)

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

8. Re: Update Format for Phone Numbers (2000)

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

9. Re: Update Format for Phone Numbers (2000)

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

10. Re: Update Format for Phone Numbers (2000)

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?

11. Re: Update Format for Phone Numbers (2000)

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•