Access 2003: Split Data into an unknown number of fields

[compooper]

Hi there,

I would like to split the data in one field: Service Code(s)


There are numerous codes in there depnding on the number of services a client has ordered, each code seperated by a comma.
That means the number of codes can vary anywhere from 1 code to a gazzilion codes for one client.

So how do you split data into an unknown number of fields?

[boblarson]

1. the data should not be stored that way. You should have another table which holds the many and has the ID of the other table (your current table) as the foreign key.

2. You don't say how you plan on using this, so what are you planning on doing with the split data?

[compooper]

I can't change the way the data is stored since its an invoice coming from another company.
What I would like to do is split up the service codes, to identify which services each client used & create budgets accordingly (by service).
The service codes are seperated by commas, so I was thinking split the services in a seperate table, sorted by client name.

[boblarson]

You could append them to a table - using something like this:

Dim varSplit As Variant
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lngCount As Long
 
Set db = Currentdb
 
Set rst = "Select [CompanyID], "[Services]" FROM TableNameHere
 
With rst
 
Do Until .EOF
   varSplit = Split(![Services], ",")
   For lngCount = 0 to UBound(varSplit)
      strSQL = "INSERT INTO TableForInsertNameHere ( CompanyID, Services ) " & _
                   "VALUES (!CompanyID, Chr(34) & varSplit(lngCount) & Chr(34));"
      CurrentDb.Execute strSQL, dbFailOnError
   Next
   .MoveNext
 
Loop
 
End With
 
rst.Close
Set rst = Nothing

So the TableNameHere refers to the table which has your CURRENT information with the services stored with commas. The TableForInsertNameHere refers to the table which you would create to put this data into (make sure the datatypes are correct). And I used CompanyID for the company and Services as the name of the field which has the comma delimited values so you'll have to replace all of the references to the names of your own fields.

I hope that helps.

[compooper]

Thanks for your help Bob.

Quick beginners question:
I just copy & paste this code into a new module right? (changing tablenamehere, tableforinsertnamehere)
Do I have to assign this a macro name? Is it a function?

[boblarson]

Thanks for your help Bob.

Quick beginners question:
I just copy & paste this code into a new module right? (changing tablenamehere, tableforinsertnamehere)
Do I have to assign this a macro name? Is it a function?

Yes, you have to change tablenamehere and the other (as well as the field names CompanyID and Services as well.

You would put it in a function to run it so if you wanted to just paste this revised one into a standard module you could change the names of the items and then call it like

SplitCodes

Function SplitCodes()
Dim varSplit As Variant
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lngCount As Long
 
Set db = Currentdb
 
Set rst = "Select [CompanyID], "[Services]" FROM TableNameHere
 
With rst
 
Do Until .EOF
   varSplit = Split(![Services], ",")
   For lngCount = 0 to UBound(varSplit)
      strSQL = "INSERT INTO TableForInsertNameHere ( CompanyID, Services ) " & _
                   "VALUES (!CompanyID, Chr(34) & varSplit(lngCount) & Chr(34));"
      CurrentDb.Execute strSQL, dbFailOnError
   Next
   .MoveNext
 
Loop
 
End With
 
rst.Close
Set rst = Nothing
End Function

Remember to name the module something other than the function name.

[compooper]

Hi Bob,

After changing all of the variables, I get an error on this line:
Set rst = "Select [ID], "[Service Code(s)]" FROM April15

[boblarson]

My bad - you need to make it this:

Set rst = db.OpenRecordset("Select [ID], "[Service Code(s)]" FROM April15)

[compooper]

Now there's a syntax error on [Service Code(s)], I've tried changing the field name but that isn't the problem

Set rst = db.OpenRecordset("Select [ID], "[Service Code(s)]" FROM April15)

[boblarson]

You shouldn't have the parentheses in the field name. But are you sure it is happening with that code there or further down?

[compooper]

I thought it has something to do with the parentheses as well, so I changed the field name. It still doesn't work. Now its highlighting the entire line, Complie Error: Syntax Error on Set rst = db.OpenRecordset("Select [ID], "[ServiceCodes]" FROM April15)

[boblarson]

You don't put [ServiceCodes] in quotes.

Sheesh, I need a vacation. I totally missed that one.

It should be:

Set rst = db.OpenRecordset "Select [ID], [ServiceCodes] FROM April15"

[compooper]

Error: Type Mismatch
Dim rst As DAO.Recordset
Set rst = "Select [ID], [ServiceCodes] FROM April15"

[boblarson]

Error: Type Mismatch
Dim rst As DAO.Recordset
Set rst = "Select [ID], [ServiceCodes] FROM April15"

You just posted the same thing again. It should be:

Dim rst As DAO.Recordset
Dim db As DAO.Database
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Select [ID], [ServiceCodes] FROM April15")