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/wa.exe?SUBED1=ACCESS-L
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
:
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
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
:
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/wa.exe?SUBED1=ACCESS-L
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
