Split Data By Each Comma (1 Viewer)

Hamdard

New member
Local time
Today, 20:40
Joined
Feb 22, 2015
Messages
14
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
 

Attachments

  • Database12.accdb
    428 KB · Views: 247

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:10
Joined
Aug 30, 2003
Messages
36,118
Double check the spelling of your field names. ;)
 

Hamdard

New member
Local time
Today, 20:40
Joined
Feb 22, 2015
Messages
14
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

Enthusiastic Amateur
Local time
Today, 16:10
Joined
Sep 21, 2011
Messages
14,044
Start checking the value if i in the debug code window. It would appear your math is out.?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:10
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 20:40
Joined
Feb 22, 2015
Messages
14
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

Wino Moderator
Staff member
Local time
Today, 09:10
Joined
Aug 30, 2003
Messages
36,118
Happy to help! I assume you fixed the SQL to enter the date in the first field.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:10
Joined
Aug 30, 2003
Messages
36,118
No problem, glad we got it working.
 

Users who are viewing this thread

Top Bottom