how to change format of data already entered

  • Thread starter Thread starter Beth A
  • Start date Start date
B

Beth A

I entered a list of phone numbers and changed the input mask to display the
numbers including the dash and parenthese, ie. (555) 555-5555. However, the
data is stored with only the numbers. What code do I need to type into an
update query so the parenthases, space and dash are stored as well? That way
when I export my date it stays in the required format.
 
You have several options - How is the data stored? Is it in a text or number
field?
Do you want to modiy the data or just change how it looks in query results,
form, or report display?

If it is in a number field and you want to store the dash and parenthese you
will need to change the field to text as a number field will not store dash
and parenthese.
You would backup the database, add the new text field, and run this query --
UPDATE [YourTable] SET [YourTable].[NewPhone] = "(" &
Left([YourTable].[OldPhone],3) & ") " & Mid([YourTable].[OldPhone],4,3) & "-"
& Right([YourTable].[OldPhone],4);
 
I entered a list of phone numbers and changed the input mask to display the
numbers including the dash and parenthese, ie. (555) 555-5555. However, the
data is stored with only the numbers. What code do I need to type into an
update query so the parenthases, space and dash are stored as well? That way
when I export my date it stays in the required format.
Click to expand...

When using an Input Mask, the mask is NOT saved with the data unless
you expressly tell it to (in the mask itself).
From Access help on the Input Mask property:

Section Description
Second Specifies whether Microsoft Access stores the literal
display characters in the table when you enter data. If you use 0 for
this section, all literal display characters (for example, the
parentheses in a phone number input mask) are stored with the value;
if you enter 1 or leave this section blank, only characters typed into
the control are stored.
So !(999) 000-0000;;_ will NOT store the mask.
but !(999) 000-0000;0;_ will.


Changing the mask will affect storage of newly entered data.
To change existing data use an Update Query:

Update YourTable Set YourTable.[PhoneField] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where [PhoneField is not null and Len([PhoneField] = 10;
 
I entered a list of phone numbers and changed the input mask to display the
numbers including the dash and parenthese, ie. (555) 555-5555. However, the
data is stored with only the numbers. What code do I need to type into an
update query so the parenthases, space and dash are stored as well? That way
when I export my date it stays in the required format.
Click to expand...

You can get the best of both - store the ten digit number, and export from a
Query using

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

to explicitly cast the phone number as a text string.
 
Use the format function in a calculated field.

Format(Telephone,"(@@@) @@@-@@@@")

If you want to permanently fix the values

UPDATE YourTable
SET Telephone = Format([Telephone],"(@@@) @@@-@@@@")
WHERE Telephone Like "??????????"

Also if you want to store formatted data change your input mask to
include the formatting characters. The second argument to the input
mask should be zero to do this. The input mask should look like:
!\(999") "000\-0000;0;_

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I am trying to do this as well.
Where do I put
Format([PhoneField],"(@@@) @@@-@@@@")
in the query?

Or are we not talking about a query?

fredg said:
I entered a list of phone numbers and changed the input mask to display the
numbers including the dash and parenthese, ie. (555) 555-5555. However, the
data is stored with only the numbers. What code do I need to type into an
update query so the parenthases, space and dash are stored as well? That way
when I export my date it stays in the required format.
Click to expand...

When using an Input Mask, the mask is NOT saved with the data unless
you expressly tell it to (in the mask itself).
From Access help on the Input Mask property:

Section Description
Second Specifies whether Microsoft Access stores the literal
display characters in the table when you enter data. If you use 0 for
this section, all literal display characters (for example, the
parentheses in a phone number input mask) are stored with the value;
if you enter 1 or leave this section blank, only characters typed into
the control are stored.
So !(999) 000-0000;;_ will NOT store the mask.
but !(999) 000-0000;0;_ will.


Changing the mask will affect storage of newly entered data.
To change existing data use an Update Query:

Update YourTable Set YourTable.[PhoneField] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where [PhoneField is not null and Len([PhoneField] = 10;
Click to expand...
 
Back
Top