Split Data By Each Comma

[Hamdard]

Hi Experts,

I have fields [DateEntered] and [Objective] in Table1 as following.

Table1
DateEntered Objective
1/2/2016 Objective 1, Objective 2, Objective 3

I need to automatically split data in the [objective] field by each comma (,). The resultant table (assume Table2) should look like the following.

Table 2
DateEntered Objective
1/2/206 Objecitve 1
1/2/206 Objecitve 2
1/2/206 Objecitve 3

I added the following VBA to a button on a Form but I get this message Too few parameters. Expected 1. What is the solution please?

Private Sub SplitData()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal As String
Dim TestArray() As String
Dim fieldNam, fieldStr As String
Dim i As Integer

sqlStr = "SELECT [DateEntered], [Objective] FROM Table1"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fieldNam = rs.Fields(0)
fieldStr = rs.Fields(1)
TestArray() = Split(fieldStr, ",")

For i = 0 To UBound(TestArray) - LBound(TestArray) + 1
If TestArray(i) <> "" Then

arrayVal = TestArray(i)

insertSQL = "INSERT INTO Table2([DateEntered], [Objective]) " _
& "VALUES(""" & [Objective] & """, """ & arrayVal & """)"
DoCmd.RunSQL (insertSQL)



End If
Next i
rs.MoveNext
Loop

 

[pbaldy]

Double check the spelling of your field names.

 

[Hamdard]

Thank you, pbaldy. It does work for the first row of data, it doesn't move the second and more rows. The debug stops at this code code ( If TestArray(i) <> "" Then ") and the message says this 'Subscript out of range'. I tried to understand it, but it appears to be above my head at the moment.

Appreciate your help on this.

 

[Gasman]

Start checking the value if i in the debug code window. It would appear your math is out.?

 

[pbaldy]

I don't understand what this is supposed to do:

For i = 0 To UBound(TestArray) - LBound(TestArray) + 1

Normally it would just be:

For i = 0 To UBound(TestArray)

 

[Hamdard]

I don't understand what this is supposed to do:

For i = 0 To UBound(TestArray) - LBound(TestArray) + 1

Normally it would just be:

For i = 0 To UBound(TestArray)

And it worked. Many thanks, pbaldy. I am back after long and can't find the THANK YOU button.

 

[pbaldy]

Happy to help! I assume you fixed the SQL to enter the date in the first field.

 

[Hamdard]

Happy to help! I assume you fixed the SQL to enter the date in the first field.

I did and thank you for the expert instructions.

 

[pbaldy]

No problem, glad we got it working.