Split Data By Comma Delimited (1 Viewer)

Hamdard

New member
Local time
Today, 22:29
Joined
Feb 22, 2015
Messages
5
Hi Experts,

In Table_A I have the following fields.

ProjectName
Sectors
Tags

Data in the Tags field is delimited by commas (,) such as the following:
ProjectName Sectors Tags
Water Supply Health Clean Water, Hygiene, Human Rights, Access to Resources

Now, I want to split and then insert data in Table_B by eliminating the delimited values so that I have each tag value in a row as a single record such as the following:
ProjectName Sectors Tags
Water Supply Health Clean Water
Water Supply Health Hygiene
Water Supply Health Human Rights
Water Supply Health Access to Resources

To do this, I wrote the following VBA code. But it returns empty records for Tags. What do I do wrong? Your expert advises is highly appreciated.

Code:
Private Sub SplitTagsButton_Click()
DoCmd.SetWarnings Off
On Error GoTo Error_Resume
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal As String
Dim TestArray() As String
Dim fieldName, fieldSector, fieldTags As String
Dim i As Integer

sqlStr = "SELECT [ProjectName], [Sectors], [Tags] FROM Table_A"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fieldName = rs.Fields(0)
fieldSector = rs.Fields(1)
fieldTags = rs.Fields(2)
TestArray() = Split(fieldTags, ", ")

For i = 0 To UBound(TestArray)
If TestArray(i) <> "" Then

arrayVal = TestArray(i)

insertSQL = "INSERT INTO Table_B([ProjectName], [Sectors], [Tags]) " _
& "VALUES(""" & fieldName & """, """ & fieldSector & """, """ & arrayVale & """)"
DoCmd.RunSQL (insertSQL)
Error_Resume:
Resume Next

End If
Next i
rs.MoveNext
Loop
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:59
Joined
Aug 30, 2003
Messages
35,248
For starters, you set one variable and use another:

arrayVal
arrayVale

This may be of assistance:

 

bastanu

AWF VIP
Local time
Today, 10:59
Joined
Apr 13, 2010
Messages
826
And you seem to have a space in the Split:
TestArray() = Split(fieldTags, ", ")
 

moke123

AWF VIP
Local time
Today, 13:59
Joined
Jan 11, 2013
Messages
2,471
You also do not set warnings true again.
Avoid setting warnings altogether and use db.execute insertSql, dbfailonerror since you've already instanced it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:59
Joined
Feb 19, 2002
Messages
32,309
But when you do set warnings, use a macro.

mSetWarningsOff = sets warnings off and hourglass on
mSetWarningsOn = sets warnings on and hourglass off

The hourglass is an annoying visual indicator that warnings are off. You can then run the on macro and fix your code.

These are the only two macros in most of my databases. It is really critical to not leave warnings off - hence using the hourglass as a visual wake up call. You will get burnt by this sooner or later when you close something you changed but didn't specifically save it before you closed the object and all your nice changes go bye-bye as they are silently discarded.
 

Users who are viewing this thread

Top Bottom