Re: extract items from list in single field

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


--
Wayne Morgan
MS Access MVP


"Pendragon" <Pendragon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:18B9A8F6-93BF-46FB-BE1B-FAAC1B739A19@xxxxxxxxxxxxxxxx
>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


.

Relevant Pages

  • The mystery of the missing (compound) variables.
    ... It's a mystery. ... What happened to the missing compound variable "array" ... members in the last record? ... DO loop, ...
    (comp.lang.rexx)
  • Re: mail() in a loop?
    ... Personally I would first scan the database, build an array of email addresses and then start sending messages. ... Tomorrow you might have 1000 users - create the email messages in the loop, spool them on the hard drive in some folder and then have a cron job who starts every 5 minutes pick the messages and send them. ... I have a simple discussion forum with about 100 members. ...
    (comp.lang.php)
  • Re: is it possible to access members of struct in loop?
    ... is it possible to access the members of a struct in a ... loop, comparable to an array? ... No (unless those 10 members happen to be an array!), ...
    (comp.lang.c)
  • Re: is it possible to access members of struct in loop?
    ... is it possible to access the members of a struct in a ... loop, comparable to an array? ...
    (comp.lang.c)
  • RE: Error 3021
    ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ... Dim strJobsAs String, strDocsAs String, varValsAs _ ...
    (microsoft.public.access.modulesdaovba)