By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Updating a column with additional digit

P: 36
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.
Dec 15 '08 #1
Share this Question
Share on Google+
3 Replies


P: 3
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.
Dec 15 '08 #2

P: 36
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.
Dec 15 '08 #3

ADezii
Expert 5K+
P: 8,638
@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):
Expand|Select|Wrap|Line Numbers
  1. Public Function fFormatDigits(varDigitsToFormat) As Variant
  2. Dim strDigitsToFormat As String
  3.  
  4. 'If not a valid Number, get outta Dodge!
  5. If Not IsNumeric(varDigitsToFormat) Then fFormatDigits = Null
  6.  
  7. 'easier to initially work with a String
  8. strDigitsToFormat = CStr(varDigitsToFormat)
  9.  
  10. 'Search for a Terminating Null Character (Chr$(0)), if found strip it
  11. If Right$(strDigitsToFormat, 1) = Chr$(0) Then
  12.   strDigitsToFormat = Left(strDigitsToFormat, Len(strDigitsToFormat) - 1)
  13. End If
  14.  
  15. If Len(strDigitsToFormat) > 8 Then
  16.   fFormatDigits = Null
  17. Else
  18.   fFormatDigits = Format(Val(strDigitsToFormat), "00000000")
  19. End If
  20. End Function
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(1)
  2. 00000001
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(12)
  2. 00000012
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(123)
  2. 00000123
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(1234)
  2. 00001234
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(12345) 
  2. 00012345
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(123456) 
  2. 00123456
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(1234567)
  2. 01234567
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(12345678)
  2. 12345678
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(123456789)
  2. Null
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatDigits(1234567 & Chr(0))
  2. 01234567
Expand|Select|Wrap|Line Numbers
  1. ? fFormatDigits(12345678 & Chr(0))
  2. 12345678
P.S. - You can pass from 1 to 8 Digits to the Function.
Dec 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.