CODEWITHSUNDEEP
ms-accessvbams-access-2013
Yohan Greenburg
Yohan Greenburg

Reputation: 333

Split Delimited Field To Multi Fields

I have an access 2013 table that houses one field with comma separated values. I have created a second table that I need to parse the results into with a structure like so

uPPID number
value1 short text
value2 short text
value3 short text
value4 short text

I am dynamically creating the table so it will always have enough "value" fields to accommodate for the number that will be parsed out. Sample data is like such:

 uppID   values
aeo031  boat, goat, hoat, moat

And I would want the field mappings to go like such

uPPID = aeo031
value1 = boat
value2 = goat
value3 = hoat
value4 = moat

How can access vba parse out a csv list from one field to many?

Upvotes: 2

Views: 315

Answers (1)

A.S.H
A.S.H

Reputation: 29332

There are probably faster/better solutions than the follwing VBA loop that inserts records one by one in the destination table. But for instance it does the job.

  • TableCSV is the name of the source table
  • TableFields is the name of the destination table
  • The constant maxValues specifies the number of fields values available
  • The query composes dynamically the INSERT INTO statement after composing the values fields; it completes it to provide all the columns, and adds the surrounding quotes '...'. (p.s. it could be simplified if we can insert without specifying all column values..)

.

Sub splitTable()
  Const maxValues As Long = 4 ' <-- Set to number of value fields in destination table
  Dim query As String, values As String, rs
  Set rs = CurrentDb.OpenRecordset("TableCSV")

  Do Until rs.EOF
    values = rs!values ' next we add commas to provide all fields
    values = values & String(maxValues - UBound(Split(values, ",")) - 1, ",")
    values = "'" & Replace(values, ",", "','") & "'"  ' 'a', 'b', '', '' etc
    query = "INSERT INTO TableFields VALUES (" & rs!uPPID & "," & values & ")"
    Debug.Print query

    CurrentDb.Execute query
    rs.moveNext
  Loop
End Sub

Upvotes: 0

Related Questions