Updating a column with additional digit

[ilikebirds]

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.

[ambmil]

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.

[ilikebirds]

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.

[ADezii]

@ilikebirds
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):

Public Function fFormatDigits(varDigitsToFormat) As Variant
Dim strDigitsToFormat As String
 
'If not a valid Number, get outta Dodge!
If Not IsNumeric(varDigitsToFormat) Then fFormatDigits = Null
 
'easier to initially work with a String
strDigitsToFormat = CStr(varDigitsToFormat)
 
'Search for a Terminating Null Character (Chr$(0)), if found strip it
If Right$(strDigitsToFormat, 1) = Chr$(0) Then
  strDigitsToFormat = Left(strDigitsToFormat, Len(strDigitsToFormat) - 1)
End If
 
If Len(strDigitsToFormat) > 8 Then
  fFormatDigits = Null
Else
  fFormatDigits = Format(Val(strDigitsToFormat), "00000000")
End If
End Function

OUTPUT:

Debug.Print fFormatDigits(1)
00000001

Debug.Print fFormatDigits(12)
00000012

Debug.Print fFormatDigits(123)
00000123

Debug.Print fFormatDigits(1234)
00001234

Debug.Print fFormatDigits(12345) 
00012345

Debug.Print fFormatDigits(123456) 
00123456

Debug.Print fFormatDigits(1234567)
01234567

Debug.Print fFormatDigits(12345678)
12345678

Debug.Print fFormatDigits(123456789)
Null

Debug.Print fFormatDigits(1234567 & Chr(0))
01234567

? fFormatDigits(12345678 & Chr(0))
12345678

P.S. - You can pass from 1 to 8 Digits to the Function.