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
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