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