Phone Number Validation/Input Mask

[Guest]

Please can someone help!!
I have a database for room bookings and I need to enter a clients phone
number. The format for the phone number could be either 01234 123456 or 0123
123 1234.
Is there any way, depending on whether there are 12 or 13 characters
including spaces, that the phone number can be formatted so the user would
type the numbers without spaces? This is my first database so simple help
would be appreciated, many thanks.

 

[Marsela]

Hi Diane!

Copy this format in Input Mask: !00000\ 000000;0;_
Thanks
Marsela
MOS-MCP

 

[Guest]

Many thanks Marsela, but this only gives me one option. I know the format
for each of them but what I need to have is an option. For example: If the
phone number has 12 numbers in it then it will format as you suggested, but,
if the phone number has 13 numbers in it then it would format as !0000\ 000\
0000;0;_
Is there a way that the input mask could decide which format to choose
depending on how many numbers are typed?

 

[John Vinson]

Is there a way that the input mask could decide which format to choose
depending on how many numbers are typed?

No. The mask property is really pretty limited.

YOu'll need to use VBA code in the BeforeUpdate event of the control
to check the formatting.

John W. Vinson[MVP]

 

[Guest]

Many thanks John, would this be hard to do? I am not sure where to start!!
Diane

 

[Ken Snell [MVP]]

It's not clear how the programming knows the difference between this
11-character number:
01234123456
which you want to be formatted as
01234 123456

and this number:
01231231234
which you want to be formatted as
0123 123 1234

If the user is to be allowed to type the numbers without spaces, then what
determines whether the resulting number string should have one or two
spaces?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken

I see your point, think I got a bit muddled, I was counting the spaces as
charachters without realising that there was the same amount of numbers!!
Is it possible that if the user keys in the spaces then it would be
formatted as (01234) 123456 or (0123) 123 1234?

Many thanks
Diane

 

[Ken Snell [MVP]]

Is it that you want the numbers displayed with the appropriate "format", or
do you want them saved into the table with that "format"?

And is the "format" now just whether the first four or the first five
numbers go inside parentheses?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

oen, thanks for you patience.
The details would be entered through the form, does the format need to be on
the table as well-maybe for consistency it would be better to format the
table as well. You are correct the "format" is now whether the first four or
the first five numbers go inside parentheses.
Diane

 

[Ken Snell [MVP]]

OK - let's go for the moment with formatting the numbers and storing them
that way.

Assuming that the person will enter the phone number with appropriate spaces
(e.g., 01234 123456 or 0123 123 1234), and assuming that you'll want
( ) characters around the first number block (up to the first space), you
could use this generic code on the textbox's AfterUpdate event to do the
formatting:

Private Sub TextBoxName_AfterUpdate()
Me.TextBoxName.Value = "(" & Left(Me.TextBoxName.Value, _
InStr(Me.TextBoxName.Value, " ") - 1) & ")" & _
Mid(Me.TextBoxName.Value, InStr(Me.TextBoxName.Value, " "))
End Sub

--

Ken Snell
<MS ACCESS MVP>