formating phone# and keeping the edit characters

[Cheswyck]

I have a phone # field defined as text and a form with an
input mask of !\(999") "000\-0000. The format comes up ok
but when I look at the data it appears as one big number,
right justified. How do I keep the '()' and '-' in the
data?

 

[fredg]

I have a phone # field defined as text and a form with an
input mask of !\(999") "000\-0000. The format comes up ok
but when I look at the data it appears as one big number,
right justified. How do I keep the '()' and '-' in the
data?

The mask does not get saved with the data unless you have specifically
coded it to.
If your mask is !\(999") "000\-0000 change it to:
!\(999") "000\-0000;0;

See Access Help files on Input mask

Unfortunately, this will only affect new entries.
You will need to use an Update query to change existing data to include
the mask:

Update YourTable Set YourTable.Phone = "(" & Left([Phone],3) & ") " &
Mid([Phone],4,3) & "-" & Right([Phone],4);

This assumes all records contain a 10 digit phone number.

 

[John S]

Just a comment:

It may be me again, but I have trouble with input masks, and prefer to
force the use of only numbers (through the keystroke event), and to format
on the form on load and control exit event and unformat (remove
formatting characters) on the gotfocus event.

I remove formatting characters on the form before_update event and record
only the numbers in text fields. I suspect as a matter of good database
practice one should not be storing (redundant) formatting characters.

John S
Aylmer, PQ

I have a phone # field defined as text and a form with an
input mask of !\(999") "000\-0000. The format comes up ok
but when I look at the data it appears as one big number,
right justified. How do I keep the '()' and '-' in the
data?

The mask does not get saved with the data unless you have specifically
coded it to.
If your mask is !\(999") "000\-0000 change it to:
!\(999") "000\-0000;0;

See Access Help files on Input mask

Unfortunately, this will only affect new entries.
You will need to use an Update query to change existing data to include
the mask:

Update YourTable Set YourTable.Phone = "(" & Left([Phone],3) & ") " &
Mid([Phone],4,3) & "-" & Right([Phone],4);

This assumes all records contain a 10 digit phone number.