Changing Field Format?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I change the format of an existing field so the data will be changed
from (011231234) to (01-123-1234). I know how to change the input mask to
make sure future entries are entered correctly, just not sure how to get the
data that's already been entered adjusted to be displayed correctly?

Thank you.
 
When you set up the Input Mask it normally applies to existing data as well -
what are you using for the Input Mask?

Sheila
www.c-i-m-s.com
MS Office training, London
 
Ugh, to simple. I had not changed the input mask yet thinking I needed to
change the format somehow first.

Thank you very much.
 
yeah - sometimes it is easy!!! Glad to help

Westaradg said:
Ugh, to simple. I had not changed the input mask yet thinking I needed to
change the format somehow first.

Thank you very much.
Click to expand...
 
When you set up the Input Mask it normally applies to existing data as well -
what are you using for the Input Mask?

Sheila
www.c-i-m-s.com
MS Office training, London
Click to expand...

Sheila,
No it doesn't change the already stored data, it just changes how the
data displays.
However, if you look at the old data elsewhere, in a report in a
query, the data is shown without the dashes.

To change existing data you'll need to run an Update query:

Update YourTable Set YourTable.FieldName =
Format([FieldName],"@@-@@@-@@@@");

Then if you add new data, using a mask that has been set to be saved
with the data (00\-00\-0000;0;_), all new data will be formatted as
wanted.
 
I see that your question has been answered, but in reality, you should not
carry the "-" in the field. It's serves no benefit and only takes up
additional disk space. You are falling into the popular trap of confusing
how data is stored and how it is presented. All values that need consistant
formatting should be carried with just the numbers. Use the Format()
function to display the value for humans.
 
Fred

I'm using Access 2003 and I can assure that when I create a report it shows
as per Input Mask whether it is old or new data

Sheila


fredg said:
When you set up the Input Mask it normally applies to existing data as well -
what are you using for the Input Mask?

Sheila
www.c-i-m-s.com
MS Office training, London
Click to expand...

Sheila,
No it doesn't change the already stored data, it just changes how the
data displays.
However, if you look at the old data elsewhere, in a report in a
query, the data is shown without the dashes.

To change existing data you'll need to run an Update query:

Update YourTable Set YourTable.FieldName =
Format([FieldName],"@@-@@@-@@@@");

Then if you add new data, using a mask that has been set to be saved
with the data (00\-00\-0000;0;_), all new data will be formatted as
wanted.
Click to expand...
 
Fred

I'm using Access 2003 and I can assure that when I create a report it shows
as per Input Mask whether it is old or new data

Sheila

fredg said:
When you set up the Input Mask it normally applies to existing data as well -
what are you using for the Input Mask?

Sheila
www.c-i-m-s.com
MS Office training, London

:

How do I change the format of an existing field so the data will be changed
from (011231234) to (01-123-1234). I know how to change the input mask to
make sure future entries are entered correctly, just not sure how to get the
data that's already been entered adjusted to be displayed correctly?

Thank you.
Click to expand...

Sheila,
No it doesn't change the already stored data, it just changes how the
data displays.
However, if you look at the old data elsewhere, in a report in a
query, the data is shown without the dashes.

To change existing data you'll need to run an Update query:

Update YourTable Set YourTable.FieldName =
Format([FieldName],"@@-@@@-@@@@");

Then if you add new data, using a mask that has been set to be saved
with the data (00\-00\-0000;0;_), all new data will be formatted as
wanted.
Click to expand...
Click to expand...

Sheila,
Her's an easy test using Access 2003 (or any version of Access).

Add a new field to a table; Text datatype (because a field storing
data as 12-123-1234 is text, not number.
Do NOT add an input mask at this point.
Enter 123456789 as it's first entry.

Back in Table Design View, add an Input Mask to the field:
000-00-0000;;_ (notice the ;; at the right, as we do not want
to save the mask with the data at this point.)
Enter (as the next record) 987654321
Notice the 2 hyphens appear in the table.
Back in table design view, change the above mask so that it will be
saved with the data
000-00-0000;0;_

Enter, as the 3rd record 445556666
Notice the hyphens appear.
Back again to design view.
Cut the input mask.
Open the table in Datasheet view.
Notice that only the 3rd record has retained the mask.
The first and second records show only the numbers without the dashes
(as they were stored).
The third record shows dashes, because it was saved with the data.

In Design View, paste the input mask back.
View the data again in Datasheet view.
The mask now shows on all records, even though only the third has the
mask stored.

In your message, when you write that the mask shows in your report,
you're seeing the effects of the mask (which carried over from the
table field mask) when you added that field to the report, not how the
data was actually stored. Delete the mask (in the report control), and
you will see the data as it was actually saved.

Let's say that field was a ZIP code field and the mask did not save
with the data, (or it was later added to the field properties).
If you add the ZIP code to the report by itself the data will display
as 98765-1234.

However, if you were to use that field in an expression, i.e.
= [City] & ", " & [State] & " " & [ZIP Code]
you will see that the ZIP code data does not include the mask unless
it was saved, i.e.
Los Angeles, CA 987651234.

Again, the way to actually change previously entered data is to run an
Update query,
 
Back
Top