Phone Number Formatting

I have a Phone Number field formatted as a text field. The field is 20
characters long and on the form the input is in the (000) 000-0000 format and
also allows for text to be inserted after the phone number such as a person's
name. On a report I want the format to be (000) 000-0000 persons name. The
input mask on the report is currently !\(999") "000\-0000;;_ but when using
that input mask it removes the () and - from the phone number in report view
if there is text (person's name) in the field following the phone number. If
there is not any text after the phone number then the () and - are displayed
in the phone number on the report view. How do I format the field on the
report to include the () and - in the phone number as well as the text in the
field following the number?

Thanks,

Scott

If you want the symbols from the input mask saved with the data, add a zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is 20
characters long and on the form the input is in the (000) 000-0000 format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but when
using
that input mask it removes the () and - from the phone number in report
view
if there is text (person's name) in the field following the phone number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field on the
report to include the () and - in the phone number as well as the text in
the
field following the number?

Thanks,

Scott

Thanks for your response. Adding the 0 didn't change the display. I started
with "0000000000 John" and it is still "0000000000 John". I need it to be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is 20
characters long and on the form the input is in the (000) 000-0000 format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but when
using
that input mask it removes the () and - from the phone number in report
view
if there is text (person's name) in the field following the phone number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field on the
report to include the () and - in the phone number as well as the text in
the
field following the number?

Thanks,

Scott

I believe I mis-read the question. My response was for changing the input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting on
the report. But this will only work for new values typed in. It will not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is 20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but when
using
that input mask it removes the () and - from the phone number in report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field on
the
report to include the () and - in the phone number as well as the text
in
the
field following the number?

Thanks,

Scott

The field in the table is a text field for a phone number that stores data
and text without symbols () or - , the data stores like xxxxxxxxxx or
xxxxxxxxxxJohn or xxxxxxxxxx Cell, it field is 20 characters long.
The form input mask is !\(999") "000\-0000;;_ but that is probably
irrelevant for my question.
In the report I need the phone number/field data to display (xxx) xxx-xxxx
with the characters. If the field only contains the numbers in the phone
number the report will display (xxx) xxx-xxxx if the report input mask is
!\(999") "000\-0000;;_ but if the field has text following the 10 numbers
then the report displays the phone number and text in the same format that it
is stored in the field without the symbols () or -. I need the phone number
in the report to display the phone number portion on the data with symbols in
the (xxx) xxx-xxxx format even when text exists in the field following the
phone number. For example:
Phone number field data xxxxxxxxxx = report display of (xxx) xxx-xxxx and
Phone number field data xxxxxxxxxxJohn = report display of (xxx)
xxx-xxxxJohn and
Phone number field data xxxxxxxxxx Cell = report display of (xxx) xxx-xxxx
Cell.

Thanks,
Scott

"Roger Carlson" wrote:

I believe I mis-read the question. My response was for changing the input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting on
the report. But this will only work for new values typed in. It will not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is 20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but when
using
that input mask it removes the () and - from the phone number in report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field on
the
report to include the () and - in the phone number as well as the text
in
the
field following the number?

Thanks,

Scott

First of all, I think it is a mistake to be storing two pieces of data in
the same field. The phone number and name should be stored separately.
This is basic database design.

Secondly, if you stored the numbers with the extra symbols, then you
wouldn't need the input mask on the textbox in the report and it would work
fine. Changing the input mask in the form will only work for new or edited
records.

If it was me and I didn't have time to restructure the database (the best
option), I'd run an update query that would add the extra characters to the
stored data and then change the input mask to store the characters for any
new data.

If all of them are without the extra symbols, you should be able to do
something like:

UPDATE YourTable SET [Phone Number] = "(" & Left([Phone Number],3) & ") " &
Mid([Phone Number],4,3) & "-" & Mid([Phone Number],7,4) & Mid([Phone
Number],11);

NOTE: make a back up of your data before you run ANYTHING!
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Scott" wrote in message
...
The field in the table is a text field for a phone number that stores data
and text without symbols () or - , the data stores like xxxxxxxxxx or
xxxxxxxxxxJohn or xxxxxxxxxx Cell, it field is 20 characters long.
The form input mask is !\(999") "000\-0000;;_ but that is probably
irrelevant for my question.
In the report I need the phone number/field data to display (xxx) xxx-xxxx
with the characters. If the field only contains the numbers in the phone
number the report will display (xxx) xxx-xxxx if the report input mask is
!\(999") "000\-0000;;_ but if the field has text following the 10 numbers
then the report displays the phone number and text in the same format that
it
is stored in the field without the symbols () or -. I need the phone
number
in the report to display the phone number portion on the data with symbols
in
the (xxx) xxx-xxxx format even when text exists in the field following the
phone number. For example:
Phone number field data xxxxxxxxxx = report display of (xxx) xxx-xxxx and
Phone number field data xxxxxxxxxxJohn = report display of (xxx)
xxx-xxxxJohn and
Phone number field data xxxxxxxxxx Cell = report display of (xxx) xxx-xxxx
Cell.

Thanks,
Scott

"Roger Carlson" wrote:

I believe I mis-read the question. My response was for changing the
input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting
on
the report. But this will only work for new values typed in. It will
not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you
give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to
be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is
20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons
name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but
when
using
that input mask it removes the () and - from the phone number in
report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field
on
the
report to include the () and - in the phone number as well as the
text
in
the
field following the number?

Thanks,

Scott

I agree.

My problem is that it is a retail database and I'm just reading the data
from SQL so I can't reconstruct or change the database design.

Thanks for your help.

Scott

"Roger Carlson" wrote:

First of all, I think it is a mistake to be storing two pieces of data in
the same field. The phone number and name should be stored separately.
This is basic database design.

Secondly, if you stored the numbers with the extra symbols, then you
wouldn't need the input mask on the textbox in the report and it would work
fine. Changing the input mask in the form will only work for new or edited
records.

If it was me and I didn't have time to restructure the database (the best
option), I'd run an update query that would add the extra characters to the
stored data and then change the input mask to store the characters for any
new data.

If all of them are without the extra symbols, you should be able to do
something like:

UPDATE YourTable SET [Phone Number] = "(" & Left([Phone Number],3) & ") " &
Mid([Phone Number],4,3) & "-" & Mid([Phone Number],7,4) & Mid([Phone
Number],11);

NOTE: make a back up of your data before you run ANYTHING!
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Scott" wrote in message
...
The field in the table is a text field for a phone number that stores data
and text without symbols () or - , the data stores like xxxxxxxxxx or
xxxxxxxxxxJohn or xxxxxxxxxx Cell, it field is 20 characters long.
The form input mask is !\(999") "000\-0000;;_ but that is probably
irrelevant for my question.
In the report I need the phone number/field data to display (xxx) xxx-xxxx
with the characters. If the field only contains the numbers in the phone
number the report will display (xxx) xxx-xxxx if the report input mask is
!\(999") "000\-0000;;_ but if the field has text following the 10 numbers
then the report displays the phone number and text in the same format that
it
is stored in the field without the symbols () or -. I need the phone
number
in the report to display the phone number portion on the data with symbols
in
the (xxx) xxx-xxxx format even when text exists in the field following the
phone number. For example:
Phone number field data xxxxxxxxxx = report display of (xxx) xxx-xxxx and
Phone number field data xxxxxxxxxxJohn = report display of (xxx)
xxx-xxxxJohn and
Phone number field data xxxxxxxxxx Cell = report display of (xxx) xxx-xxxx
Cell.

Thanks,
Scott

"Roger Carlson" wrote:

I believe I mis-read the question. My response was for changing the
input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting
on
the report. But this will only work for new values typed in. It will
not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you
give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to
be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is
20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons
name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but
when
using
that input mask it removes the () and - from the phone number in
report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field
on
the
report to include the () and - in the phone number as well as the
text
in
the
field following the number?

Thanks,

Scott

If I understand you correctly this is for a report that you are designing,
and you can not change the input method or the table data. Well I would then
change the controlsource for the textbox in the report. Maybe some thing
like this: ="(" & Left([Phone Number],3) & ") " & Mid([Phone Number],4,3) &
"-" & Mid([Phone Number],7,4) & Mid([Phone Number],11)
Barrowing a snippit from Roger Carlson's reply

"Scott" wrote:

I agree.

My problem is that it is a retail database and I'm just reading the data
from SQL so I can't reconstruct or change the database design.

Thanks for your help.

Scott

"Roger Carlson" wrote:

First of all, I think it is a mistake to be storing two pieces of data in
the same field. The phone number and name should be stored separately.
This is basic database design.

Secondly, if you stored the numbers with the extra symbols, then you
wouldn't need the input mask on the textbox in the report and it would work
fine. Changing the input mask in the form will only work for new or edited
records.

If it was me and I didn't have time to restructure the database (the best
option), I'd run an update query that would add the extra characters to the
stored data and then change the input mask to store the characters for any
new data.

If all of them are without the extra symbols, you should be able to do
something like:

UPDATE YourTable SET [Phone Number] = "(" & Left([Phone Number],3) & ") " &
Mid([Phone Number],4,3) & "-" & Mid([Phone Number],7,4) & Mid([Phone
Number],11);

NOTE: make a back up of your data before you run ANYTHING!
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Scott" wrote in message
...
The field in the table is a text field for a phone number that stores data
and text without symbols () or - , the data stores like xxxxxxxxxx or
xxxxxxxxxxJohn or xxxxxxxxxx Cell, it field is 20 characters long.
The form input mask is !\(999") "000\-0000;;_ but that is probably
irrelevant for my question.
In the report I need the phone number/field data to display (xxx) xxx-xxxx
with the characters. If the field only contains the numbers in the phone
number the report will display (xxx) xxx-xxxx if the report input mask is
!\(999") "000\-0000;;_ but if the field has text following the 10 numbers
then the report displays the phone number and text in the same format that
it
is stored in the field without the symbols () or -. I need the phone
number
in the report to display the phone number portion on the data with symbols
in
the (xxx) xxx-xxxx format even when text exists in the field following the
phone number. For example:
Phone number field data xxxxxxxxxx = report display of (xxx) xxx-xxxx and
Phone number field data xxxxxxxxxxJohn = report display of (xxx)
xxx-xxxxJohn and
Phone number field data xxxxxxxxxx Cell = report display of (xxx) xxx-xxxx
Cell.

Thanks,
Scott

"Roger Carlson" wrote:

I believe I mis-read the question. My response was for changing the
input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting
on
the report. But this will only work for new values typed in. It will
not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you
give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to
be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is
20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons
name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but
when
using
that input mask it removes the () and - from the phone number in
report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field
on
the
report to include the () and - in the phone number as well as the
text
in
the
field following the number?

Thanks,

Scott

The result is exactly what I was looking for!

Thank you,

Scott

"Nyx37" wrote:

If I understand you correctly this is for a report that you are designing,
and you can not change the input method or the table data. Well I would then
change the controlsource for the textbox in the report. Maybe some thing
like this: ="(" & Left([Phone Number],3) & ") " & Mid([Phone Number],4,3) &
"-" & Mid([Phone Number],7,4) & Mid([Phone Number],11)
Barrowing a snippit from Roger Carlson's reply

"Scott" wrote:

I agree.

My problem is that it is a retail database and I'm just reading the data
from SQL so I can't reconstruct or change the database design.

Thanks for your help.

Scott

"Roger Carlson" wrote:

First of all, I think it is a mistake to be storing two pieces of data in
the same field. The phone number and name should be stored separately.
This is basic database design.

Secondly, if you stored the numbers with the extra symbols, then you
wouldn't need the input mask on the textbox in the report and it would work
fine. Changing the input mask in the form will only work for new or edited
records.

If it was me and I didn't have time to restructure the database (the best
option), I'd run an update query that would add the extra characters to the
stored data and then change the input mask to store the characters for any
new data.

If all of them are without the extra symbols, you should be able to do
something like:

UPDATE YourTable SET [Phone Number] = "(" & Left([Phone Number],3) & ") " &
Mid([Phone Number],4,3) & "-" & Mid([Phone Number],7,4) & Mid([Phone
Number],11);

NOTE: make a back up of your data before you run ANYTHING!
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Scott" wrote in message
...
The field in the table is a text field for a phone number that stores data
and text without symbols () or - , the data stores like xxxxxxxxxx or
xxxxxxxxxxJohn or xxxxxxxxxx Cell, it field is 20 characters long.
The form input mask is !\(999") "000\-0000;;_ but that is probably
irrelevant for my question.
In the report I need the phone number/field data to display (xxx) xxx-xxxx
with the characters. If the field only contains the numbers in the phone
number the report will display (xxx) xxx-xxxx if the report input mask is
!\(999") "000\-0000;;_ but if the field has text following the 10 numbers
then the report displays the phone number and text in the same format that
it
is stored in the field without the symbols () or -. I need the phone
number
in the report to display the phone number portion on the data with symbols
in
the (xxx) xxx-xxxx format even when text exists in the field following the
phone number. For example:
Phone number field data xxxxxxxxxx = report display of (xxx) xxx-xxxx and
Phone number field data xxxxxxxxxxJohn = report display of (xxx)
xxx-xxxxJohn and
Phone number field data xxxxxxxxxx Cell = report display of (xxx) xxx-xxxx
Cell.

Thanks,
Scott

"Roger Carlson" wrote:

I believe I mis-read the question. My response was for changing the
input
mask on the form control (textbox) so it would store the extra characters
with in the field. Then you wouldn't need *any* inputmask or formatting
on
the report. But this will only work for new values typed in. It will
not
affect existing records.

I'm not following what you're doing and how you're doing it. Can you
give
some additional details?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com



"Scott" wrote in message
...
Thanks for your response. Adding the 0 didn't change the display. I
started
with "0000000000 John" and it is still "0000000000 John". I need it to
be
"(000) 000-0000 John".

Thanks,

Scott

"Roger Carlson" wrote:

If you want the symbols from the input mask saved with the data, add a
zero
between the two semi-colons:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Scott" wrote in message
...
I have a Phone Number field formatted as a text field. The field is
20
characters long and on the form the input is in the (000) 000-0000
format
and
also allows for text to be inserted after the phone number such as a
person's
name. On a report I want the format to be (000) 000-0000 persons
name.
The
input mask on the report is currently !\(999") "000\-0000;;_ but
when
using
that input mask it removes the () and - from the phone number in
report
view
if there is text (person's name) in the field following the phone
number.
If
there is not any text after the phone number then the () and - are
displayed
in the phone number on the report view. How do I format the field
on
the
report to include the () and - in the phone number as well as the
text
in
the
field following the number?

Thanks,

Scott