converting number data to text and formating

[Daniel M]

I have a table with serial number data in it. We started the table with only
the last 6 digits. As a number the leading zeros were stripped. This was fine
for 1 product but we have since added products. ie: 02-6digits was a product
now we are adding 03-6digits as another product. In order to handle this we
simply added the 03 in front of the 6 digits for the other product. The zero
was stripped and we were left with 7 digits. Fine. anything 6 digits or less
was an 02- product.

We are now going to be scanning the barcodes in to the system and want to
record the whole number. Can someone help my come up with a script of some
kind to convert the existing data?

Examples of data
4001 to convert to 02-004001
201002 to convert to 02-201002
3000123 to convert to 03-000123

I know i have to convert it to a text field but i could use some help on
converting the existing data. We do not want to split the field into 2 as it
would break other forms/reports/tables. thanks.

 

[Bob Quintal]

I have a table with serial number data in it. We started the table
with only the last 6 digits. As a number the leading zeros were
stripped. This was fine for 1 product but we have since added
products. ie: 02-6digits was a product now we are adding
03-6digits as another product. In order to handle this we simply
added the 03 in front of the 6 digits for the other product. The
zero was stripped and we were left with 7 digits. Fine. anything 6
digits or less was an 02- product.

We are now going to be scanning the barcodes in to the system and
want to record the whole number. Can someone help my come up with
a script of some kind to convert the existing data?

Examples of data
4001 to convert to 02-004001
201002 to convert to 02-201002
3000123 to convert to 03-000123

I know i have to convert it to a text field but i could use some
help on converting the existing data. We do not want to split the
field into 2 as it would break other forms/reports/tables. thanks.

First ADD a text field to the table. This will be (temporary)
Run an update query that sets the contents of the table to cstr([Name
of Numeric field])
Check that the codes in the temp field are good coupies of the
numeric field.

Once that's done, you need to change the numeric field to text type.
and run a query to replace the data with nulls.

Then you create and run a series of queries that test for specific
conditions of the temp field, and add the missing prefix and leading
zeroes. one condition in the query is that the target field is null.
That prevents overwriting data that you've already fixed.

so you'd do a query that checks if the length of the temporary field
is 7 digits, and does
"0" & left([temporary Field],1) & "-" & mid([temporary field],2)
then a query that does "02-" & right("00000"& [temporary field],6)

Check again and then delete the temporary field.

 

[KARL DEWEY]

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
tRY THIS --
IIF(Len([YourField])<6, "02", Right("0" & Left([YourField], 1), 2) &
"-"& Right("000" & Right([YourField], 6), 6)