Format Phone Number with or without extension? 1

[booboo0912]

Hello! I need to format a phone number using the following format, (###) ###-####, which I can do using an Input Mask, but there may or may not be an extension that follows that also needs to be formatted. Can anyone tell me how a newbie like myself would do this?
Thanks in advance!
booboo

 

[Isadore]

boo: why don't you break out your extension into an independent field?

 

[booboo0912]

Thanks for responding, Isadore! This is a new request from the users, the database has been around for years, and to add another field to the table/form would create havoc for the users, not to mention space issues on the form. With all the relationships/queries/reports involved, I thought it would be easier to format the field values on an After Update.
Thanks!

 

[Isadore]

boo: Well, good luck. I'm sure it can be done one way or another. As a general rule though I have found (using Access for many years now) that "space on the form" has never been an issue and secondly, adding an additional field has never creating any serious problem. Unless of course you have a few hundred queries you have to modify, etc... Now, changing table relationships, etc.., well, there you may have some time involved. But I routinely go into extensive databases and add "yes/no" fields, this or that, and so long as the field is just attendant to the data, never has been a problem.

My impression is though that if you use a formatting statement like you propose, won't you be "forced" into putting something in the input mask? What happens when there is no extension? will an input mask accept "Null"?

At any rate, if you do think that it'd be too much of a problem then I'm sure you can dig up something. Perhaps someone will drop by here on Tek-Tips with an answer. I'll keep my eyes open.

 

[booboo0912]

The users requested an input mask, but obviously an input mask won't work b/c not every phone number entered into the form will have an extension associated with it. I don't have time to reformat the form/subform to make another field fit, or to update the many queries and reports that would need to display the added field. And so my search for a script that will format the data continues....
Thanks!
booboo

 

[Isadore]

boo: How about this. Instead of searching for an input mask, which may exist yet, you could put in a little code and "validate" the entry as correct. That would be simple enough, e.g.,

say a user puts in
(850)699-9834 x. (rejected by code)
850-699-9834 (accepted by code)
850-699-9834 x. 32 (accepted by code)
etc..etc... deciding what you can live with.

In this way the user can put in what's needed, and if there is a problem (no space, not a space, one too many numbers, dash in the wrong place, etc.. you can send up a Msgbox reminding the user of the proper format and to try again. Just an idea.

 

[booboo0912]

That's exactly what I'm trying to do...based on the data entered, figure out how many characters the user entered, and split them to the requested format - (###) ###-####, then whatever is left over, put as the extension. I'm not looking for an input mask to do this, at least I don't think it can be done using an input mask. I can figure out how to do this in Javascript, but I'm not a VB person so I'm having problems trying to use VB syntax.
Thanks!

 

[Isadore]

boo: Send me the logic and I'll convert it into vba for ya.

 

[booboo0912]

Thanks Isadore! Here's what I have...if you know of a better way (using VB), please let me know!

function chkNumber() {
var strNum = phone.value; //phone = value entered
var strNumLen = strNum.length;

if(strNumLen <= 10) {
var strNum1 = strNum.substring(0,3)
var strNum2 = strNum.substring(3,6)
var strNum3 = strNum.substring(6,10)
var strPhone = &quot;(&quot; + strNum1 + &quot &quot; + strNum2 + &quot;-&quot; + strNum3;
phone.value = strPhone
}
else if(strNumLen > 10) {
var strNum1 = strNum.substring(0,3)
var strNum2 = strNum.substring(3,6)
var strNum3 = strNum.substring(6,10)
var strNum4 = strNum.substring(10,(strNumLen + 1))
var strPhone = &quot;(&quot; + strNum1 + &quot &quot; + strNum2 + &quot;-&quot; + strNum3 + &quot; ext. &quot; + strNum4;
phone.value = strPhone
}
}

 

[Isadore]

Looks good boo. I'm tied up at the moment but hang in there, I'll parse this out into vba; give me a couple of hours and I'll post back.

 

[booboo0912]

Thanks Isadore!

 

[Isadore]

boo: Not to sound disconerting but before we put this in code consider the following:

Let strPhone be text, value entered

Step 1. What are the possibilities?

Reject (R) if:
1. There are not 2 parenthesis
2. Len < 10
3. If Right(strPhone,1) = &quot;.&quot;
4. If Left(strPhone,1) <> &quot;(&quot;

Accept if:
1. There are 2 parenthesis and no spacing between numbers
and does not end with a period. Note that spacing on
either side of a hypen is ok (15) and if the spacing
occurs after the prefix (16). The ones below that
would be accepted includes 2,6,7,10,11,15 & 16


1. (850) 689-1159 x. 342 Len: 21 A
2. (850) 689-1159 Len: 14 A
3. 8506891159 Len: 10 R
4. 850689-1159 Len: 11 R
5. 850-689-1159 Len: 12 R
6. (850)689-1159 Len: 13 A
7. (850)-689-1154 Len: 14 A
8. (850) 689-1159 x. Len: 17 R
9. (850) 689-1159 ext. Len: 19 R
10. (850)689-1159 ext. 1 Len: 18 A
11. (850) 689-1159 x. 1 Len: 17 A
12. (850) 689-1159 ext. 1 02 Len: 24 R
13. (850) 689-1159 ext. 10 2 Len: 24 R
14. (850) 6 89-1159 Len: 15 R
15. (850) 689- 1159 Len: 15 A
16. (850) 689 1159 Len: 14 A
17. ?
18. ?
19. ?
20. ?

The ones that are accepted but fall short can
be modified and formatted correctly in code.
So, what it boils down to is how much leeway
are you going to accept?

Turn this over a bit, add or take away from the
logic, and repost. For example, rejecting No. 4
because it doesn't have 2 parenthesis does not
have to be a fatal error, so we can either reject
it or correct it.

While you're mulling this over I'm going to do a
little diggin. Of course, all of this begs for
an input mask, eh?

 

[booboo0912]

Wow...I should have told you the customer is going to enter only numbers (they want to get away from typing the '( )' and '-')...sorry about that!! So if they type in 1234567890 1234, using the code i gave you, the result would be (123) 456-7890 ext. 1234...

Does that sound better? I know it's not the best way of handling it, but there are only 2 people that enter data.

 

[Isadore]

boo: good. That does make a difference. Ok, will get back when I have a tested solution working; I'd like to solve this one - next post - we'll have it!

 

[Isadore]

boo:

Here it is:

!\(999&quot &quot;000\-0000\ CCCC;0;#

this input mask requires the 10 digit entry, and the &quot;C&quot; slots are optional, so you can enter
1
12
123
1234 for the extension

...ta da. Give a lazy man a job and he'll find the quickest solution! Later guy.

 

[booboo0912]

THANK YOU, ISADORE!! This will definitely work! I didn't realize it could be done as an input mask! Hope you have a great week! Thanks again!!!