I am driving myself mad trying to get something simple to work. SOmeone here is going to laugh at what I am missing....

I have a string returned from reading a file (its going to form part of an automated import process). Being a bit of a pig it's seperated with semicolons (;)

I need to break it down into fields from this and recall the split method from my asp days. Dug out my trusty old wrox 'begining asp' to refer to the reference section in the back (missing from begining access 2000 vba) but I cannot get it to work.Ive gone through the msdn examples but they are a little lack-lusture.

I seem to be running into dificulty in assigning the return from split into an array of any kind. I think this may be simply down to not knowing the exact syntax for assigning the return from split (which will be unknown at runtime and dynamic) to an array.

I have tried:>
newarray = Split(impGetString, ";")
newarray() = Split(impGetString, ";")

Anyone have a solution????

I perform this method in several functions in an application I'm working on to split an employee name into the first and last name elements. The syntax in your post is correct. The only thing I don't know is whether or not you declare your array. Do you have any other information such as an error that is raised or anything else that could help determine the cause?

When you say the syntax is correct - which one!

(1) newarray = Split(impGetString, ";")
OR
(2) newarray() = Split(impGetString, ";")

I do declare the array as I'm using option explicit but you hay have highlighted an error. The reference I had on it was dim newarray() as integar - which I thought was odd myself....

I will run it around again later and come back with the errors it generates.

Thanks for your input.

I apologize for omitting that bit of information before. The proper syntax is as your exhibit (1).

I don't think it should be a problem if you declare your array as an array of integer values. The one thing to remember is that if the string you are breaking down is a string of numeric values, the integer data type is only valid for numbers from -32,768 to 32,767. If any of the values in the string exceed either of these limits, you will receive a run time error. Also, the integer data type is only for whole numbers, that means no values to the right of the decimal. The same goes for the long data type. If the values in your string contain rational numbers (values to the right of the decimal point), you should declare a data type of single or double.

One other thing to consider might be to declare your array as variant and let the application decide the sub-data type for each element for the array.

I hope this helps. Good luck.

Scrub the post below - got the problem. It was in the variable definition. I opted for: Dim tmpArray() As Variant which gave mismatch error where as Dim tmpArray As Variant worked. Ummm.
When is an array not an array.........

IGNORE<><><> LEFT IN SITU FOR REFERENCE <><><><>

Still no go. Throws Error 13 (Type mismatch and highlights the last line): Have tried converting the contents of the variable impGetString to a string prior to executing the command as well. No joy. On the first run variable impGetString contains "E:\_MASTER_SHARES\MUSIC\2003\Alex Parks - Over Conscious(cd).mp3;041.50;Alex Parks;Over Conscious(cd);version;album name;pop;2003;comment;0;0;0;;0;0;;0;0;;0;0;;0;0;;0 ;0;;0;0;;0;0;;0;0;;0;0;;0;0;;0;0;;0;0;;0;0;;0;0;;0 ;0;;0;0;;0;0;;0;0;;0;0;"

Dim tmpArray() As Variant

Open "d:\data_projects\import.txt" For Input As #1
Input #1, impGetString ' gets one line from text file
' split up string into sub data
tmpArray = Split(impGetString, ";")

You should drop the parentheses when you declare a Variant array. The Variant type is able to hold an array:

Dim tmpArray As Variant

Alternatively, declare the array as a String:

Dim tmpArray() As String

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Sorted this one out - many thanks for all of your help.