update data in table for new Format

[Daniel M]

I have a table with several thousand records. currently i have serial numbers
stored as number but i now need to change the format. currently it is 4-8
digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more
digits). I need this new value to store the "-" in the field. I know i can
switch the field to a text field and this works but i need a way to convert
existing data to the new format. I tried a mask format but it starts at the
beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21
and i need 00-004021.

Any ideas on this one? Thanks.

 

[John W. Vinson]

I have a table with several thousand records. currently i have serial numbers
stored as number but i now need to change the format. currently it is 4-8
digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more
digits). I need this new value to store the "-" in the field. I know i can
switch the field to a text field and this works but i need a way to convert
existing data to the new format. I tried a mask format but it starts at the
beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21
and i need 00-004021.

Any ideas on this one? Thanks.

I'd suggest adding a new 9-character Text field; run an Update query updating
it to

Format([numberfield], "00-000000")

If this serial number is in multiple table you'll need to change it in all of
them; and if it's involved in any relationships, you'll need to delete the
relationship between the Number fields (select the join line in the
relationships window and press the Delete key) and reestablish it between the
text fields.