splitting string

[sadiewms]

Hello!

I have a flat file that I'm trying to get into a relational Access 2003
DB. One of the fields in the flat file has a list of names separated
by ";". I'd like to loop through them and create new records for each
in a table relational DB. ( name1; name2; name3; name4) I'm getting
stuck on splitting the string. Because there may be up to 6 names in
the original field I can't use the Left(), Mid() functions. I've also
tried using Split() but Access doesn't recognize the function. I'm
quite new to VB and Access so I'm not sure of the functions that are
available. Any ideas?

Thanks

Sadie

[Lyle Fairfield]

Split should be available in any version of Access >= 2000 (9).
If for some reason (I can't think of one) you need a UDF you could use
one of the many you could find with a Google search. Here's mine ...
not so extensively tested:

' change this if it does not meet your needs
Const SplitLimit As Long = 4096

Public Function SplitB(ByVal SplitString As String, _
Optional ByVal Delimiter As String = " ", _
Optional Element As Long) As Variant
Static aSplit() As Variant
Dim Position As Long
If Element = 0 Then ReDim aSplit(SplitLimit)
Position = InStr(SplitString, Delimiter)
If Position = 0 Then
aSplit(Element) = SplitString
ReDim Preserve aSplit(Element)
SplitB = aSplit
Else
aSplit(Element) = _
Trim(Left(SplitString, Position - 1))
SplitB = _
SplitB(Mid$(SplitString, Position + 1), Delimiter, Element + 1)
End If
End Function