Split Record seperated by "/ "

[jdawg_1989]

I have a query that when runs return data like the following
Field 1 | Field 2
Jon | 123456, 12345678, 234567, 987654
Bob | 457891, 73892, 635279

There will only ever be a maximum of 8 records separated by ", " in Field 2 and so far I have tried the following mudule with no luck.

Public Function ParseSNE(AnyString As String, Element As Integer) As Integer  
Dim MyArray 
'/Expunge to an array 
MyArray = Split(AnyString, "/ ")  

'/return the port requested 
ParseSNE = MyArray(Element - 1)  

End Function

When using the above in a query I am getting Run-time Error '13' and it is falling over on:

ParseSNE = MyArray(Element - 1)

Thanks in advance.

 

[gemma-the-husky]

its not integer, its a string

try

ParseSNE = clng(trim(MyArray(Element - 1)))


and i just realised, in your split function you want a comma not a slash - but I expect Paul pointted that out!

 

[pbaldy]

Your thread title and code describe a delimiter of "/", but your description and example use a delimiter of ",". Which is it?

 

[jdawg_1989]

Re: Split Record seperated by ", "

Apologies, I did try to edit the title, it was ","

Thanks for your input, I have managed to solve by using the method posted here:
http://support.microsoft.com/kb/95608

and gemma-the-husky - For the record, I just tried your solution and it did work, Thanks.

 

[gemma-the-husky]

I wrote a load of stuff like that - pos and instr, in A97. Not sure if split was available then! or maybe i was learning!