1,105,633 Community Members

VBA for MS Access question - split function

Member Avatar
jay.barnes
Junior Poster in Training
57 posts since Jun 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hello!

I'm working on VBA within MS Access XP, and can't seem to get the proper usage of the split function down. I've been trying to make this work, but I keep getting the "Run-time error '9': Subscript out of range" error.

Since the code of my project is kind of long, I'll try to stick to the most relevant part- can someone please explain to me where I'm going wrong?

'Declare your Dims:
Dim InputString As String
Dim NewArray As Variant
Dim NewArrayKey0 As String

'Give the InputString variable a value:
InputString = "input string"

'Split the InputString variable into two parts,
'separated by the default delimiter, the space:
NewArray = Split(InputString)

'Assign the first key of the array to NewArrayKey0 variable:
NewArrayKey0 = NewArray(0)

'Display the value of NewArrayKey0 (It should be the word "input") in a message box:
MsgBox NewArrayKey0

Thank you!
-jay

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

On line 14 you have to use the cstr function to force the Variant to be a string:

NewArrayKey0 = cstr(NewArray(0))
Member Avatar
jay.barnes
Junior Poster in Training
57 posts since Jun 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi, BitBit, thanks for assisting,

I tried your suggestion, but I still get me the same error, highlighting line 14.

Could it be the data type of the "NewArrayKey0", when it's declared? It is a string, but should I set it up as another data type?

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

Wow, I'm sorry my suggestion didn't help. I actually went so far as to build a little dummy VB6 app AND a dummy MSAccess app, and it worked fine for me both ways. I have no other suggestions.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: