split a text field into multiple columns in Access 2007

I'm not sure what you mean by "columns". Generally, you'd insert each of those items into a Row in a table, not in columns across a single Row.

You can use the Split function to do this:

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTAble")
arr = Split(rst("YourField"), ",")

For i = 0 to Ubound(arr)
  '/do something here
Next i

that's pretty much what i have so far, it's the "do something here" part that i'm getting stuck at. What i want it to do is create a new column for each item number and insert the item number into the field. Attached is the code i have so far which creates the columns, i'm not sure how to insert the item number value into each field as it works it's way through the table though. Does that make sense?

Sub test()
DoCmd.SetWarnings (WarningsOff)

Dim s As String
Dim a() As String
Dim i As Integer

Dim strFieldName As String

s = "001, 002, 003, 004, 005"


a = split(s, ",")

For i = 0 To UBound(a, 1)
strFieldName = "Item" & i

CreateField "Slips", strFieldName

Debug.Print a(i)

Next i


End Sub


Function CreateField( _
      ByVal strTableName As String, _
      ByVal strFieldName As String) _
      As Boolean

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Creates a Text field, other data types listed
   '
   ' strTableName : Name of table in which to create the field
   '
   ' strFieldName : Name of the new field to add to table
   ' Returns True on success, false otherwise
   '
   'USAGE: CreateField "TABLENAME", "FIELDNAME"

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim fld As DAO.field
   Dim tdf As DAO.TableDef

   Set Db = Application.CurrentDb
   Set tdf = Db.TableDefs(strTableName)

   ' First create a field with data type = Text
   Set fld = tdf.CreateField(strFieldName, dbText)

   'A few Alternate datatypes: for DAO - Note: The listed Complex data types require
         ' Access 2007 or higher
   'Long = dbLong or dbComplexLong
   'Single = dbSingle or dbComplexSingle
   'Double = dbDouble or dbComplexDouble
   'Integer = dbInteger
   'Decimal = dbDecimal or dbComplexDecimal
   'Text = dbText or dbComplexText
   'Memo = dbMemo
   'Currency = dbCurrency
   'Yes/No = dbBoolean
   'Date = dbDate

   ' Appending the field
   With tdf.Fields
      .Append fld
      .Refresh
   End With
   CreateField = True

ExitHere:
   Set fld = Nothing
   Set tdf = Nothing
   Set Db = Nothing

   Exit Function

errhandler:
   CreateField = False

   With Err
 
   End With

   Resume ExitHere

End Function

Select allOpen in new window

Creating a column for each field does NOT make sense. As I stated earlier, you really should be putting these in Rows of your table.

What exactly does the data represent?

Each row represents a work ticket. Each work ticket is associated with multiple Items (as indicated by the [Item Numbers] column). Each item number is 3 digits (ie. 001, 002) separated by a comma. I would like to take each item number and store it in a separate column for each work ticket. The first screen shot(singlecolumn), shows how the data is currently stored. The second, (multicolumn) shows how i would like it.

SingleItemNumberColumn.jpg
MultipleItemNumberColumn.jpg

Forensicon,

I urge you to re-read LSMConsulting's comment http:#a31405421, and to take his advice to heart.  Putting this into separate columns is a stupendously bad idea.  All the principles of proper database design would have you place these items into separate rows, and not separate columns.

Patrick

I agree with both of you that creating multiple columns is a bad idea. The problem i'm running into though is that all this information is coming out of sharepoint and i'm trying to make do with what i have. So i understand, you guys are suggesting that instead of creating multiple columns to store the item numbers, that i create a new row for each item number. EG.

ItemNumber                       WorkTicketNumber
001, 002, 003                         1234

Would result in:

ItemNumber                       WorkTicketNumber
001                                     1234
002                                     1234
003                                     1234


Is that correct?

That's the idea :)

i'm beginning to get it., Attached is the code i have so far which works well to loop through the whole table [Slips] and parse out each item number for each row. My thought is to create another table called [ItemNumberWorkTickets] which will have 2 columns, "ItemNumber" and "SP_WorkTicketID". As i loop through my Slips table i want to pull out each ItemNumber and WorkTicketID and insert it into the [ItemNumberWorkTickets] table. This way i can run join queries later as you guys suggested. What would be the sql statement to do this? Thanks for the good advice guys.

Sub split1()

Dim strFieldName As String
Dim sql As String

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")

Do While Not rst.EOF


arr = split(rst("[Item Numbers]"), ",")

    For i = 0 To UBound(arr)
    Debug.Print arr(i)

sql = "INSERT INTO ItemNumberWorkTickets ( ItemNumber, SP_WorkTicketID )SELECT " & arr(i) & " AS Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips;"
DoCmd.RunSQL sql
  
  

    Next i
rst.MoveNext
Loop

End Sub

Select allOpen in new window

any thoughts?

i figured it out using the code provided by lsm and other posts. i included it below for reference. Thanks to both experts.

Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter

Set rsOrig = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")
Set rsNew = CurrentDb.OpenRecordset("ItemNumberWorkTickets")

If rsOrig.RecordCount <> 0 Then

'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter
vArr = split(rsOrig("[Item Numbers]"), ",")

'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
Debug.Print vArr(i)
.AddNew
.Fields("SP_WorkTicketID") = rsOrig("SP_WorkTicketID")
.Fields("[ItemNumber]") = Trim(vArr(i))
.Update
End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

MsgBox "Complete"


End Function

Select allOpen in new window

based on LSM's suggestions i was able to use the code provided and other code found on EE.