Code to get phone numbers in canonical format?

[deko]

I have a (Access 2003) contact management database where the user can
double-click a contact's phone number in a form and have the Windows Phone
Dialer dial the number. The problem is the number has to be in canonical
format or dialing rules won't be applied (cf. MSKB Article 318575). I don't
want to use an Input Mask because users like to put comments after the
number, like: "985-983-0098 ext. 980 - Mike B."

I thought there might be a function or other canned code to put a phone
number into canonical format - i.e. [phone]. ???

If not, I'll need to brush up on regular expressions and gin something up on
my own...

Thanks in advance.

[PC Datasheet]

How about having separate fields on your form for Area Code, Number and
Extension? If your data entry form is a single form, these three textboxes
could be unbound with your real telephone number field hidden. You could
then concatenate the telephone number from the AfterUpdate event of each of
these in case the user changed any one of them and set your hidden bound
telephone number equal to the concatenated number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"deko" <de*****@hotmail.com> wrote in message
news:K9******************@newssvr29.news.prodigy.c om...

I have a (Access 2003) contact management database where the user can
double-click a contact's phone number in a form and have the Windows Phone
Dialer dial the number. The problem is the number has to be in canonical
format or dialing rules won't be applied (cf. MSKB Article 318575). I don't want to use an Input Mask because users like to put comments after the
number, like: "985-983-0098 ext. 980 - Mike B."

I thought there might be a function or other canned code to put a phone
number into canonical format - i.e. [phone]. ???

If not, I'll need to brush up on regular expressions and gin something up on my own...

Thanks in advance.

[deko]

> How about having separate fields on your form for Area Code, Number and

Extension? If your data entry form is a single form, these three textboxes
could be unbound with your real telephone number field hidden. You could
then concatenate the telephone number from the AfterUpdate event of each of these in case the user changed any one of them and set your hidden bound
telephone number equal to the concatenated number.

Well, it's a subform datasheet and users like the free-form, in-cell
editing/commenting of the numbers. The best way to do it, I think, is just
grab whatever string is in the field, parse it into the right format, and
send it to the dialer, transparent to the user. I'm sure someone has
already written the code I need... so I thought I'd ask...

[deko]

here's a first crack at it:

Dim i As Integer
Dim n, p As String
strDial = Trim$(Forms!frm0!frm0Telephone.Form!TelNumber)
For i = 1 To Len(strDial)
n = Mid(strDial, i, 1)
If n Like "#" Then p = p & n
Next i
strDial = "+1" & Format(Left(p, 10), "(@@@)&&&-&&&&")
Debug.Print strDial

-------

Assumes an area code (and nothing else) is prefixed to every phone number.
should probably strip out any leading 1 or 0...