Updating a column with additional digit

I've searched the forums with no success.

I have a column of data containing 7 digit and 8 digit numbers.

I would like to update all the 7 digit numbers and add a 0 in front of them so they become 8 digit numbers.

I have tried an update query and Find and Replace however I have had no success

iif(([line_item]) ="????????,"0???????,)


Any Suggestions?

Thanks.

re: Updating a column with additional digit

as long as your fields only have 7 or 8 digits, and they all have to be 8 digits, try this:

iif(len([line_item])=7, "0" & [line_item]), [line_item])


you can also use a query:
update <<table>> set [line_item] = "0" & [line_item] where len([line_item]) < 8

If your values have several varying lengths, but must all be 8 digits, you would just run this query until it stops. Your field must be a text field for this to work.

re: Updating a column with additional digit

Thank You.

Using:
iif(len([line_item])=7, "0" & [line_item]), [line_item])

I was able to find out that after my 7th digit, I had a Null Character.
Therefore the exact code didn't work.

Still trying to beat my head into getting this null character calculated.

...=7 & " ", as well as =7 & NULL, are not working.

re: Updating a column with additional digit

I hastily wrote a custom Function which will accept a Long Integer, or a Long Integer with a Terminating Null. It will strip the Null Terminator, if it exists, and should return the proper results as indicated by the demo code below. I leave it up to you to make sure that a SINGLE or DOUBLE is not passed to the Function (it will trap a non-numeric or String and return Null):
OUTPUT:
P.S. - You can pass from 1 to 8 Digits to the Function.