Formatting phone numbers

  • Thread starter Thread starter Kathy
  • Start date Start date
K

Kathy

In my table my input mask for a phone number is great,
shows for example (207) 543-2345. But when I use Word
mail merge the phone number comes out 2075432345. Help,
please.
 
In the query that your mailmerge uses add a new field for
phone number. Use this
HomePhoneMod: "(" & Left$([homephone],3) & ") " &
Mid$([homephone],4,3) & "-" & Right$([homephone],4)

Jim
 
In my table my input mask for a phone number is great,
shows for example (207) 543-2345. But when I use Word
mail merge the phone number comes out 2075432345. Help,
please.
Click to expand...

The mask you are using to input the phone numbers is not set up to be
saved with the data.

To save the mask (on future entries only), add a zero between the
semicolons (at the right portion of the mask) so that it looks like
this:
;0;_

Future entries will include the () and -.
See Access help on the Input Mask property

You'll need to use an Update query to modify the existing data first:

Update TableName set TableName.PhoneField = "(" & left([PhoneField],3)
& ") " & mid([PhoneField],4,3) & "-" & Right([PhoneField],4);
 
Fredg,

Thanks for all your help in this newsgroup. I have learned
quite a bit from you.

In this case why would you want to store the ()- characters
when a combination of the format and Input mask suffices?
(I am referencing you update query) Or are these characters
not really stored. In all my forms and reports I use the
format and Input mask. In this case( a Mail Merge) I did
have to put those characters in a temporary field for the
word document merge.

Jim
-----Original Message-----
In my table my input mask for a phone number is great,
shows for example (207) 543-2345. But when I use Word
mail merge the phone number comes out 2075432345. Help,
please.
Click to expand...

The mask you are using to input the phone numbers is not set up to be
saved with the data.

To save the mask (on future entries only), add a zero between the
semicolons (at the right portion of the mask) so that it looks like
this:
;0;_

Future entries will include the () and -.
See Access help on the Input Mask property

You'll need to use an Update query to modify the existing data first:

Update TableName set TableName.PhoneField = "(" & left([PhoneField],3)
& ") " & mid([PhoneField],4,3) & "-" & Right([PhoneField],4);

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Fredg,

Thanks for all your help in this newsgroup. I have learned
quite a bit from you.

In this case why would you want to store the ()- characters
when a combination of the format and Input mask suffices?
(I am referencing you update query) Or are these characters
not really stored. In all my forms and reports I use the
format and Input mask. In this case( a Mail Merge) I did
have to put those characters in a temporary field for the
word document merge.

Jim
-----Original Message-----
In my table my input mask for a phone number is great,
shows for example (207) 543-2345. But when I use Word
mail merge the phone number comes out 2075432345. Help,
please.
Click to expand...

The mask you are using to input the phone numbers is not set up to be
saved with the data.

To save the mask (on future entries only), add a zero between the
semicolons (at the right portion of the mask) so that it looks like
this:
;0;_

Future entries will include the () and -.
See Access help on the Input Mask property

You'll need to use an Update query to modify the existing data first:

Update TableName set TableName.PhoneField = "(" & left([PhoneField],3)
& ") " & mid([PhoneField],4,3) & "-" & Right([PhoneField],4);

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...
The choice to store the mask is of course an optional one.
If you are aware of how the data is stored, you can certainly format
the data whenever it's needed.
The question asked was how to get 1234567890 to merge in Word as
(123) 123-4567.
My reply was to explain why using the mask by itself (without setting
the ;0;) did not include the parenthesis and dash with the stored
table data.
Also I gave a method of adding the ()- to the existing data already
stored, using an Update query.

Does it have to be done this way? Absolutly not.

Advantage of doing it this way, in my opinion, is it doesn't require
any additional effort to have the phone field print in a useful
format. The same would be true if the wanted format was xxx.xxx.xxxx
or xxx xxx-xxxx, or whatever. Once in a useful format, no other work
is needed whenever the field is used in other reports or documents.

Disadvantage of doing it this way... a few extra characters stored for
each phone number in the table.
 
One reason for including the formatting in what you store is when you're
dealing with international applications. (nnn) nnn-nnnn is okay for North
America, but it isn't for other countries.
 
Back
Top