extract items from list in single field

[Pendragon]

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[Wayne Morgan]

Try using the Split() function to change the delimited string into an array.
You would then loop through the members of the array and write one record to
the table for each member.

Example:
Public Sub TestSplit()
Dim db As DAO.Database, rstRead As DAO.Recordset, rstWrite As DAO.Recordset
Dim strResult() As String, i As Long
Set db = CurrentDb
Set rstRead = db.OpenRecordset("TableName", dbOpenSnapshot)
Set rstWrite = db.OpenRecordset("DestinationTableName", dbOpenDynaset)
If Not (rstRead.BOF And rstRead.EOF) Then
'If both are true, no records were in the table
rstRead.MoveFirst
Do Until rstRead.EOF
strResult() = Split(CStr(rstRead!ItemList), ",")
For i = LBound(strResult) To UBound(strResult)
With rstWrite
.AddNew
!ID = rstRead!ID
!ItemList = strResult(i)
.Update
End With
Next i
rstRead.MoveNext
Loop
rstRead.Close
rstWrite.Close
Set rstRead = Nothing
Set rstWrite = Nothing
Set db = Nothing
End Sub

 

[Pendragon]

After a little more research, I found a great set of links on Allen Browne's
website. On the Access MVP website, there's code to do exactly this.

 

[John Nurick]

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

 

[Pendragon]

Thanks to both John and Wayne. I love how there are always different ways of
doing things, so I will try out both options.

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[Toni-Ann Mistretta]

Hi,

I am hainvg trouble finding that info on Allen Browne's site. can you be
more spefic

Thanks

Toni-Ann

 

[mary r]

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross