Separate Data

R

rml

I have a table with a field that has data that looks like the following:

N0345, H4354, 48574, 48577

How can I create a query that would separate that data into their own fields?

Field one would have N0345 the next would have H4354 etc...

Thanks.
 
J

John Spencer

This can be done with difficulty.

What you really need to do is to store the values in a table with one row
for each of the values.

What is the maximum number of values that might be stored.

I would use a custom vba function to do this.

SELECT getSplit([MultiField],0) as TheFirst
, getSplit([MultiField],1) as TheSecond
, getSplit([MultiField],2) as TheThird
, getSplit([MultiField],3) as TheFourth
FROM YourTable

"UNTESTED FUNCTION follows. Copy and paste this into a VBA module and save
it.
Public Function getSplit(TheString, iPos)
Dim vAr as Variant

If Len(TheString & "") = 0 Then
getSplit = Null
Else
vAr = Split(theString, ",")
If iPos <= UBound(vAr) Then
getSplit = vAr(iPos)
Else
getSplit = Null
End if

End Function

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top