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

Newbie, If Statement & Run Time Error 424

P: 9
Hi,
I'm importing a text file which contains a barcode. Once imported I add a column to the table & copy the barcode into the new column (So far I've done all of this using code).

I want to re-format the new column so that all the barcodes are 13 digits long (some are 7, some are 6 etc, etc). Where any barcodes are not 13 digits long, I want to add leading zero's until they are all 13 digits long. This is where I'm getting stuck. I wrote a real basic piece of code but when I run it I get a "run time error 424, "Object Required" message.

Here is an example of what I wrote:

Expand|Select|Wrap|Line Numbers
  1. Function Test()
  2. Dim OneZero As String
  3. Dim TwoZero As String
  4. OneZero = "0"
  5. TwoZero = "00"
  6.  
  7. If Len(AllTitles.[ASDABarcode]) = 12 Then
  8. AllTitles.[ASDABarcode] = OneZero & AllTitles.[ASDABarcode]
  9. End If
  10. End Function
  11.  
I'm a complete novice really just trying to learn some basics in coding etc to maybe help automate some tasks; so any help or guidance will be appreciated.
Thanks,
BEP
Apr 27 '10 #1

✓ answered by ADezii

@BlackEyedPea
Execute the following UPDATE Query:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE AllTitles SET [ASDABarcode] = Format([ASDABarcode], '0000000000000');"
  4.  
  5. CurrentDb.Execute strSQL, dbFailOnError
Before Executing Query:
Expand|Select|Wrap|Line Numbers
  1. ASDABarcode
  2. 1
  3. 12
  4. 123
  5. 1234
  6. 12345
  7. 123456
  8. 1234567
  9. 12345678
  10. 123456789
  11. 1234567890
  12. 12345678901
  13. 123456789012
  14. 1234567890123
After Executing Query:
Expand|Select|Wrap|Line Numbers
  1. ASDABarcode
  2. 0000000000001
  3. 0000000000012
  4. 0000000000123
  5. 0000000001234
  6. 0000000012345
  7. 0000000123456
  8. 0000001234567
  9. 0000012345678
  10. 0000123456789
  11. 0001234567890
  12. 0012345678901
  13. 0123456789012
  14. 1234567890123

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,698
@BlackEyedPea
Execute the following UPDATE Query:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE AllTitles SET [ASDABarcode] = Format([ASDABarcode], '0000000000000');"
  4.  
  5. CurrentDb.Execute strSQL, dbFailOnError
Before Executing Query:
Expand|Select|Wrap|Line Numbers
  1. ASDABarcode
  2. 1
  3. 12
  4. 123
  5. 1234
  6. 12345
  7. 123456
  8. 1234567
  9. 12345678
  10. 123456789
  11. 1234567890
  12. 12345678901
  13. 123456789012
  14. 1234567890123
After Executing Query:
Expand|Select|Wrap|Line Numbers
  1. ASDABarcode
  2. 0000000000001
  3. 0000000000012
  4. 0000000000123
  5. 0000000001234
  6. 0000000012345
  7. 0000000123456
  8. 0000001234567
  9. 0000012345678
  10. 0000123456789
  11. 0001234567890
  12. 0012345678901
  13. 0123456789012
  14. 1234567890123
Apr 27 '10 #2

P: 9
ADezii, you are a god amongst men! You would probably laugh if you knew how much time I'd spent trying to do that, & you manage it in minutes!
Thanks very much, really appreciate it.
Apr 27 '10 #3

NeoPa
Expert Mod 15k+
P: 31,768
Learning curves are always steepest at the start. Stick with it & you'll find it all so much easier in a short while :)

Welcome to Bytes!
Apr 27 '10 #4

Post your reply

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