Split data into many fields

[echorley]

When I receive test data from our Dept. of Ed., individual student answers for each multiple choice question are all kept in one field:

Field1
ABCAACDCCDAC
ACCAADDCCAAB
ACCABDDCCAAB
etc...

How can I split this one field into many fields, one for each answer. I would like to automate this in VBA. I have researched the Split function, but cannot seem to wrap my arms around it.

 

[KenHigg]

I think using the mid() function would work:

MyNewFielda = mid([Field1],1,1)
MyNewFieldb = mid([Field1],2,1)
etc...

 

[DCrake]

The main crux of the code would be

For X = 1 To Len(Answers)
Answer = Mid(Answers,X,1)
Next

If you have one table that has all the answers in one field and you want to split them into individual fields in another table. You would first open the first table as a recordset and copy the answers to a string.

Next you would open the many fields table and use the Rs.AddNew approach

Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim ArrayAnswers()
Dim iCnt As Integer
Dim StrAnswer As String

Set Rs1 = CurrentDb.OpenRecordset("TblAnswersInFull")

If Not Rs1.EOF And Not Rs1.BOF Then
   Rs1.MoveLast
   ReDim ArrayAnswers(Rs1.RecordCount,11)
   
   Do Until Rs1.EOF

       
         ArrayAnswers(iCnt,0) = Rs1(StudentID")
         For X = 1 To 10
           StrAnswer = Mid(Rs1("Answers"),X,1)
           ArrayAnswers(iCnt,X) = StrAnswer
         Next
         Rs1.MoveNext
         iCnt = iCnt +1
    Loop
    Rs1.Close
End If

At this point you will have all the student id's and answers in individual elements of an array. Next open up the second recordset and append them to the table

Set Rs2 = CurrentDb.OpenRecordset("TblTests")

For x = 0 to iCnt -1
    Rs2.AddNew
    Rs2("StudentID") = ArrayAnswers(x,0)
    Rs2("Answer1") = ArrayAnswers(x,1)
    Rs2("Answer2") = ArrayAnswers(x,2)
    etc
    etc
    Rs2.Update
Next

Rs2.Close
Set Rs1 = Nothing
Set Rs2 = Nothing

Remember this is all aircode and is untested. Don't forget to replace my table and field names with the correct ones.

David

 

[echorley]

Thanks I will give that a try.