Split a field into many

[echorley]

When I receive student testing data in CVS format, there is one field with
all of the student answers. For example, ABACCCDACADBBBAC.

How can I split that one field into one field per answer?

 

[Stefan Hoffmann]

hi,

When I receive student testing data in CVS format, there is one field with
all of the student answers. For example, ABACCCDACADBBBAC.
How can I split that one field into one field per answer?

You can't. You need to loop over the string, e.g.:

Dim Position As Long

For Position = 1 To Len(yourString)
MsgBox Mid(yourString, Position, 1)
Next Position



mfG
--> stefan <--

 

[John Spencer]

Use the Mid Expression in a query

SELECT Mid(AnswerField,1,1) as Q1
, Mid(AnswerField,2,1) as Q2
, MId(AnswerField,3,1) as Q3
, Mid(AnswerField,4,1) as Q4
....
FROM YourTable


In design view you would have multiple calculated fields.
Field: Q1: Mid(AnswerField,1,1)

Add in any other fields you wish to use.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================