Input Masks

[Guest]

My database contains many tables which in turn contain many phone number
fields. I have created separate fields to house US/Canada numbers versus
international numbers. The international number fields do not contain any
input masks or formatting requirements. So the user is expected to enter that
during input. (99% of the numbers entered in this database will be US/Canada
and no record will contain both a US/Canada and an international number). For
the US/Canada number fields, I can set the input mask in the table to be
either:

!\(999") "000\-0000;0;_

Or

!\(999") "000\-0000;;_

I have a couple of questions:

1. Is it correct that if I use !\(999") "000\-0000;;_ , I am storing only
the digits and not the parentheses and hypens?

2. If I need to use this data in a mail merge Microsoft Word document, will
the second option not contain any formatting; and therefore, would it be
better to use the first option even though it required more space in the
database?

3. On my Access reports, I have concatenated the US/Canada number and the
international number to conserve space on the report. (Perhaps this would
have been better done with If statements, but I am not familiar with those.)
This results in whichever number is present being printed. With this
concatenated field, should the Input Mask Property be blank for this field on
this report? Will the formatting for the US/Canada number come through
anyway, even if the Input Mask Property is blank?

Thanks in advance for any help.

 

[John Vinson]

My database contains many tables which in turn contain many phone number
fields. I have created separate fields to house US/Canada numbers versus
international numbers. The international number fields do not contain any
input masks or formatting requirements. So the user is expected to enter that
during input. (99% of the numbers entered in this database will be US/Canada
and no record will contain both a US/Canada and an international number). For
the US/Canada number fields, I can set the input mask in the table to be
either:

!\(999") "000\-0000;0;_

Or

!\(999") "000\-0000;;_

I have a couple of questions:

1. Is it correct that if I use !\(999") "000\-0000;;_ , I am storing only
the digits and not the parentheses and hypens?

Yes. If you put a zero between the semicolons it will store the
punctuation; leave it blank and it won't.

2. If I need to use this data in a mail merge Microsoft Word document, will
the second option not contain any formatting; and therefore, would it be
better to use the first option even though it required more space in the
database?

That's a tossup. I'll usually store just the numbers and create a
Query using the Format() function to cast the number into a string:

ExpPhone: Format([phone], "\(@@@\) @@@-@@@@")

Just setting the Format property or a mask will not help with exports,
you do need the explicit casting.

3. On my Access reports, I have concatenated the US/Canada number and the
international number to conserve space on the report. (Perhaps this would
have been better done with If statements, but I am not familiar with those.)
This results in whichever number is present being printed. With this
concatenated field, should the Input Mask Property be blank for this field on
this report? Will the formatting for the US/Canada number come through
anyway, even if the Input Mask Property is blank?

For this you will CERTAINLY need the Format() function. You can use an
expression such as

IIF(IsNull([NAPhone]), [INTLPhone], Format([NAPhone], "\(@@@\)
@@@-@@@@")

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps