Split string in to multiple records

[Bart2013]

Hi Guys,

Can anybody help me with a script, of help to to an existing threat.
I know very little about VBA so I need something to copy / paste....

Probably very simple but not for me:

With a query I select the following (example) fields from a table:

[ID] [FGRP]
X1 5,8 (This string is in an memo-field and is always divided by a comma)
Y8 1,3,18 (Same)
Etc, etc

The script should split de field [FGRP] in to multiple rows and should be added to a (existing) table. Like so:

[ID] [FGRP]
X1 5
X1 8
Y8 1
Y8 3
Y8 18

The field [FGRP] can vary from empty up to appr. 150 numbers, always separated by a comma.

I have been searching the web for two day's now and I can't find anything useful to copy paste because I just don't know enough about advanced VBA.

If you can help me you are the hero of the month!

Cheers,

Bart

 

[DavidAtWork]

This can be done by iterating through a recordset of all the fields in your query, you then for each record in the recordset, you take each field in turn and examine the string value character by character looking for the next comma or the end of the string. When you hit a comma or the end of the string, you have reached the end of that little string value (ignoring the comma of course) since the previous comma and that value has to inserted into your new table along with the field name which in your text above appears to be X1 or Y8
There are a couple of ways you can work the string to extract each new record value. You can get the length of the string and use a For Loop looking at each character or you could use the Instr function to look for a comma and then use the Mid function to extract that portion and then move along the string knowing the position of the last comma.

David

 

[Bart2013]

Thanks David,
Maybe I can figure that out, but how do I get the results in an new row?

Bart

 

[pr2-eugin]

Hello Bart2013, Welcome to AWF..

David has given you a start.. You first need to get the Data right.. So if you get stared with it.. i.e. get the right data, then you can advance to the next step of adding it to the Table.. Along with David's suggestion I would also add the use of Split function.. Easy and simple.. As they say..

All roads lead to Rome

 

[DavidAtWork]

Actually PR2's suggestion would be even better, TBH I've not used this function before but it looks perfect for what you want to do. For each value return from the array, you just need to use some INSERT sql such as:

insertSQL = "INSERT INTO yourTable(ID, FGRP) " _
& "VALUES(""" & rs.Fields(0) & """, """ & arrayVal & """)"
Docmd.RunSQL(insertSQL)
Here rs.Fields(0) is the first field value in the current record form the recordset
(using Dim rs As recordset)
arrayVal is the variable you can use to capture each array element from what the Split function returns

David

 

[DavidAtWork]

using your query example in your OP, and PR2's suggestion of the Split function, try this code:

Sub splitStringIntoRecords()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal as String
Dim TestArray() As String
Dim fieldNam, fieldStr As String
Dim i As Integer

sqlStr= "SELECT [ID], [FGRP] FROM yourTable"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fieldNam = rs.Fields(0)
fieldStr = rs.Fields(1)
TestArray() = Split(fieldStr, ",")

For i = 0 To UBound(TestArray) - LBound(TestArray) + 1
If TestArray(i) <> "" Then

arrayVal = TestArray(i)

insertSQL = "INSERT INTO yourTable(ID, FGRP) " _
& "VALUES(""" & fieldNam & """, """ & arrayVal & """)"
Docmd.RunSQL(insertSQL)

End If
Next i
rs.Movenext
Loop


End Sub

You just need to adjust the code for your table and field names.

David

 

[Bart2013]

Thanks All! but sorry, this is all like Hungarian to me...

I did the following:
- I have made a test table called "destination"
- I have made the query [ID] [FGRP] on table "source"
- I have made a form which shows these fields based on this query
- I have made a button on the record in the form, and pasted your code into the screen for the VBA (On click)
- I changed the field and table names into mine
- Saved the lot, pressed the button on the form..... off course, no good.

Can you please advise me on how to make a script like this work?

Thanks!

 

[pr2-eugin]

Hey there, try the following steps..

The code is,

Public Sub addToTable()
    Dim rstObj As DAO.RecordSet, dbObj As DAO.Database
    Dim InsertSQL As String
    Set dbObj = CurrentDB()
    Set rstObj = dbObj.OpenRecordSet("[COLOR=Blue]yourQueryName[/COLOR]")
    Do While Not rstObj.EOF
        Dim memArr() As String
        memArr = Split(rstObj.Fields("ID"),",")
        For i=0 To UBound(memArr)
            insertSQL = "INSERT INTO [COLOR=Blue]yourTable[/COLOR]([COLOR=Blue]ID[/COLOR], [COLOR=Blue]FGRP[/COLOR]) VALUES(""" & rstObj.Fields("ID") & """, """ & memArr(i) & """)"
            Docmd.RunSQL(insertSQL)
        Next
        rstObj.MoveNext
    Loop
End Sub

Change Blue bits to match your DB design..

 

[Bart2013]

Thanks all, its working fine!

 

[cwillson]

Hi peeps,

I'm faced with exactly the same issue, only I have far more columns in my table than the OP.

I've followed your very comprehensive layman's instructions pr2-eugin (thank you!!) but am getting an error when I try to save the newly created module, on the line:

Set rstObj = dbObj.OpenRecordset("SCO_AC_test")

SCO_AC_test query:

SELECT [ID], [Manuscript ID - Original], [Manuscript Type], [Manuscript Title], [Manuscript Status], [Original Submission Date], [Submitting Author Person ID], [Corresponding Author Person ID], [Author Names], [Due Date of Latest Revision], [Due Date of Invited Paper], [Tracking Number - Invited Paper], [Topic]
FROM SCO_Art_Con_RAW;

It's the [Author Names] column which has the delimited values - semicolon in my instance though.

Please help!!

Cheers,

Chris