If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
converting number data to text and formating
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. |
#2
|
|||
|
|||
converting number data to text and formating
=?Utf-8?B?RGFuaWVsIE0=?= wrote
in : 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. -- Bob Quintal PA is y I've altered my email address. |
#3
|
|||
|
|||
converting number data to text and formating
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) -- Build a little, test a little. |
Thread Tools | |
Display Modes | |
|
|