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

Subscript out of range error

P: 48
I am using a function to delete the "@" from address field. this function is also supposed to remove the unnecessary "0" from the beginning of a street address. i am this function in a simple query but this error "Subscript out of range"
Expand|Select|Wrap|Line Numbers
  1.  
  2. 1.Public Function StripDup(ByVal varWith As Variant) As String
  3. 2.      Dim intX As Integer, intY, intZ As Integer
  4. 3.      Dim strWith As String
  5.  
  6. 4.     If IsNull(varWith) Then
  7. 5.         StripDup = ""
  8. 6.       Exit Function
  9. 7.     End If
  10.  
  11. 8.     StripDup = Trim(varWith)
  12. 9.     varWith = Split(StripDup, " ")
  13. 10.     For intX = 0 To UBound(varWith) Step 1
  14. 11.        If Left(varWith(intX), 1) = "@" Then
  15. 12.            varWith(intX) = LTrim(varWith(intX))
  16. 13.        End If
  17. 14.     Next intX
  18.  
  19. 15.    For intY = 0 To Len(varWith(0)) Step 1
  20. 16.        If Left(varWith(0), 1) <> "0" Then Exit For 17.Else varWith(0) = LTrim(varWith(0))
  21. 18.    Next intY
  22. 19.    StripDup = ""
  23. 19.    For intZ = 0 To UBound(varWith)
  24. 20.    StripDup = StripDup & " " & varWith(intZ)
  25. 21.    Next intZ
  26. 22.    StripDup = LTrim(StripDup)
  27.  
  28. 23. End Function
  29.  
Attached Files
File Type: zip Data1.zip (373.7 KB, 37 views)
Dec 6 '11 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What line is it erroring out on? I see multiple places where it can go out of range.
Dec 7 '11 #2

P: 48
I've fixed the out of range error how i am not getting the result that all i am getting is the value in table. my goal is to remove the @ from the address and also delete the zeros "0" from the beginning of the address.
Dec 7 '11 #3

Rabbit
Expert Mod 10K+
P: 12,366
That's a different question. Please post how you solved the out of range error and then create a new thread for your new question.
Dec 7 '11 #4

P: 48
I have the wrong variable in line 20. I just replaced IntxX with Intz
Dec 7 '11 #5

P: 48
i have posted the question under How to delete unwanted characters from an address field.
Dec 7 '11 #6

Post your reply

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