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

splitting string

P: n/a

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?



Feb 16 '06 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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
aSplit(Element) = _
Trim(Left(SplitString, Position - 1))
SplitB = _
SplitB(Mid$(SplitString, Position + 1), Delimiter, Element + 1)
End If
End Function

Feb 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.