Separate Data

  • Thread starter Thread starter rml
  • Start date Start date
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.
 
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
..
 
Back
Top