Split Data By Each Comma (1 Viewer)

Hamdard

New member
Local time
Today, 07:52
Joined
Feb 22, 2015
Messages
4
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: 24

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:22
Joined
Aug 30, 2003
Messages
34,812
Double check the spelling of your field names. ;)
 

Hamdard

New member
Local time
Today, 07:52
Joined
Feb 22, 2015
Messages
4
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, 03:22
Joined
Sep 21, 2011
Messages
7,470
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
Yesterday, 19:22
Joined
Aug 30, 2003
Messages
34,812
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, 07:52
Joined
Feb 22, 2015
Messages
4
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
Yesterday, 19:22
Joined
Aug 30, 2003
Messages
34,812
Happy to help! I assume you fixed the SQL to enter the date in the first field.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:22
Joined
Aug 30, 2003
Messages
34,812
No problem, glad we got it working.
 

Users who are viewing this thread

Top Bottom