Splitting One Table Field into Five Table Fields 1

[infdarsas]

Hi,

I have a field in a table that currently contains the multiple choice options for tests. Here's an example:

A. 4
B. 6
C. 8
D. 10
E. 12

What I want to do is to split this field up into 5 different fields. Currently, it looks like this:

ItemAnswers
A. 4
B. 6
C. 8
D. 10
E. 12

I want to it look like this:

ItemAnswerA ItemAnswerB ItemAnswerC ItemAnswerD ItemAnswer E
A. 4 B. 6 C. 8 D. 10 E. 12

In the original field, the different choice options are separated by a hard return, if that helps. How would I achieve this?

Thanks

 

[Remou]

Hi
Here is a query (SQL view) that may help:
[tt]SELECT Mid([ItemAnswers],1,2) AS ItemAnswerA, Mid([ItemAnswers],5,2) AS ItemAnswerB, Mid([ItemAnswers],9,2) AS ItemAnswerC, Mid([ItemAnswers],13,2) AS ItemAnswerD, Mid([ItemAnswers],17,2) AS ItemAnswerE INTO NewTable
FROM MyTable;[/tt]

 

[infdarsas]

Hi Remou,

When I tried that query, it split them really weird. For ItemAnswerA, in the new table, the values were all "A." For ItemAnswerB, they were on the line of "nn." For C, they were "I." For D, "ur." And For E, they were "do."

The query did split the field, but not the way I wanted it to. What could be wrong? Do you need more information?


Thanks

 

[PHV]

Do you need more information?
The real content of ItemAnswers, i.e. how are separated the A., B., C., D. and E. items ?
With vbCr, vbLf, vbCrLf, ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infdarsas]

Here's an example of one:

A. Ecess value of ticket.
B. IRS contact.
C. Tax-exempt number.
D. Tax form number.


They were entered on a form. After every choice, they would hit the enter key on the keyboard.


Hope this is enough information

 

[PHV]

In a standard code module create this function:

Public Function getItemAnswerX(strAnswers, strX As String)
If Trim(strAnswers & "") = "" Then Exit Function
Dim intStart As Integer, strItem As String, strChar As String
strItem = strX & ". "
intStart = InStr(strAnswers, strItem)
If intStart > 0 Then
  For intStart = intStart + 3 To Len(strAnswers)
    strChar = Mid(strAnswers, intStart, 1)
    If Asc(strChar) < 32 Then Exit For
    strItem = strItem & strChar
  Next intStart
End If
getItemAnswerX = strItem
End Function

And now your query:
SELECT getItemAnswerX([ItemAnswers],'A') AS ItemAnswerA
, getItemAnswerX([ItemAnswers],'B') AS ItemAnswerB
, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infdarsas]

Thanks a lot! That worked just like I wanted it to.


Thanks again.