Hello Friends,
So here is my question.
I have a table with data Name,Title,Products.
Produccs column has lots of products against each name seperated by ";"
Example : Name,Title,Product1;Product2
what i want is Row1 :Name,Title,Product1
Row2 :Name,Title,Product2 and so on.
i have a code but it does not seem to work.
here it is :
Function test()
Dim rstTable As DAO.Recordset
Dim i As Integer
Dim strNames() As String
Set rstTable = currentdb.TableDefs("source").OpenRecordset
Do While Not rstTable.EOF
'first check if the field holds an ; if not then assume we dont need any new rows
If InStr(1, rstTable( 0 ), ";") > 0 Then 'assumes names are in the 1st col of table
'split the values
strNames = Split(rstTable( 0 ), ";") 'assumes names are in the 1st col of the table
For i = 0 To UBound(strNames)
rstTable.Edit
rstTable.AddNew
rstTable( 0 ) = strNames( i )
rstTable.Update
Next i
End If
rstTable.MoveNext
Loop
End Function
Regards,
Tejas
So here is my question.
I have a table with data Name,Title,Products.
Produccs column has lots of products against each name seperated by ";"
Example : Name,Title,Product1;Product2
what i want is Row1 :Name,Title,Product1
Row2 :Name,Title,Product2 and so on.
i have a code but it does not seem to work.
here it is :
Function test()
Dim rstTable As DAO.Recordset
Dim i As Integer
Dim strNames() As String
Set rstTable = currentdb.TableDefs("source").OpenRecordset
Do While Not rstTable.EOF
'first check if the field holds an ; if not then assume we dont need any new rows
If InStr(1, rstTable( 0 ), ";") > 0 Then 'assumes names are in the 1st col of table
'split the values
strNames = Split(rstTable( 0 ), ";") 'assumes names are in the 1st col of the table
For i = 0 To UBound(strNames)
rstTable.Edit
rstTable.AddNew
rstTable( 0 ) = strNames( i )
rstTable.Update
Next i
End If
rstTable.MoveNext
Loop
End Function
Regards,
Tejas