parse string to multi records

[LGarcia]

Hi all,
I have a table with 2 fields. Field1 is the primary key. Field2 is a string
of text separated by commas. I need to parse this to create a multi record.
The data resembles this:
Car | blue, green, black, gray
bike | red
boat | blue, red

I need it to be multi-record:
car | blue
car | green
car | red
bike | red
boat | blue
boat | red

Can anyone help?
Thanks,
LGarcia

 

[Stefan Hoffmann]

hi,

I have a table with 2 fields. Field1 is the primary key. Field2 is a string
of text separated by commas. I need to parse this to create a multi record.
The data resembles this:
Car | blue, green, black, gray
bike | red
boat | blue, red

Use Split() to parse your field and a VBA loop over your data, e.g.
something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim i As Long
Dim s() As String

Set db = CurrentDb
Set rs = db.OpenRecordset("yourTable", dbOpenSnapshot)
Do While Not rs.Eof
s() = Split(rs![yourField], ",")
If UBound(s) > 0 Then
For i = 0 To UBound(s)
db.Execute "INSERT INTO yourTable " & _
"VALUES ('" & rs![yourField1] & "', '" & s(i) & "')"
Next i
End If
rs.MoveNext
Loop


mfG
--> stefan <--

 

[LGarcia]

Worked!
Thanks!!!!
LGarcia

hi,

I have a table with 2 fields. Field1 is the primary key. Field2 is a
string of text separated by commas. I need to parse this to create a
multi record.
The data resembles this:
Car | blue, green, black, gray
bike | red
boat | blue, red

Use Split() to parse your field and a VBA loop over your data, e.g.
something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim i As Long
Dim s() As String

Set db = CurrentDb
Set rs = db.OpenRecordset("yourTable", dbOpenSnapshot)
Do While Not rs.Eof
s() = Split(rs![yourField], ",")
If UBound(s) > 0 Then
For i = 0 To UBound(s)
db.Execute "INSERT INTO yourTable " & _
"VALUES ('" & rs![yourField1] & "', '" & s(i) & "')"
Next i
End If
rs.MoveNext
Loop


mfG
--> stefan <--