Splitting a field

[Jill]

I have a field that has anywhere from 0 to 5 entries, that are separated by
a comma. I'd like to put them into (up to) 5 separate fields. Each entry is
the same length, 10 characters (plus the comma).

Is there a way to write a query to do this?

Thanks, J~

 

[CompGeek78]

I have a field that has anywhere from 0 to 5 entries, that are separated by
a comma. I'd like to put them into (up to) 5 separate fields. Each entry is
the same length, 10 characters (plus the comma).

Is there a way to write a query to do this?

Thanks, J~

You'll want to use the Mid function to accomplish this.

Something along the lines of:

SELECT Mid(data,1,9) AS firstpart, Mid(data,11,9) AS secondpart, Mid
(data,21,9) AS thirdpart, Mid([data],31,9) AS fourthpart, Mid([data],
41,9) AS fifthpart
FROM Table;

Keven Denen

 

[Bob Quintal]

I have a field that has anywhere from 0 to 5 entries, that are
separated by a comma. I'd like to put them into (up to) 5 separate
fields. Each entry is the same length, 10 characters (plus the
comma).

Is there a way to write a query to do this?

Thanks, J~

You can use the left() and mid() functions to do this.
However, I suspect that you would be better off in putting the data
into 5 rows in a child table rather than 5 columns in one row.

 

[John W. Vinson]

I have a field that has anywhere from 0 to 5 entries, that are separated by
a comma. I'd like to put them into (up to) 5 separate fields. Each entry is
the same length, 10 characters (plus the comma).

Is there a way to write a query to do this?

Thanks, J~

I agree with Bob that you're just going from the fire to the frying pan with
this change... but will add that in VBA (not in a query though) you can use
the Split() function to extract the pieces. See the VBA help for Split or post
back with a description of the context and what you want to accomplish.

 

[David W. Fenton]

I have a field that has anywhere from 0 to 5 entries, that are
separated by a comma. I'd like to put them into (up to) 5 separate
fields. Each entry is the same length, 10 characters (plus the
comma).

Would that be:

0123456789, 0123456789, 0123456789, 0123456789, 0123456789

or:

0123456789,0123456789,0123456789,0123456789,0123456789

If your destination fields are Field1, Field2, etc. (any names will
do):

INSERT INTO tblSplit ( Field1, Field2, Field3, Field4, Field5 )
SELECT Left([Text],10), Mid([Text],13,10), Mid([Text],25,10),
Mid([Text],37,10), Mid([Text],49,10)
FROM tblSplit
WHERE tblSplit.Text Is Not Null;

Adjust the first numeric argument of Mid() if you don't have spaces
after the columns.

But, of course, you really don't want to do this. Instead, you want
to put the data in a separate table that links to the table with the
field with the comma-separated list.

This can also be done without having a fixed-length, using InStr()
to find the location of the first comma and using that as the length
of what you return. But that results in such complex SQL that it's
usually a lot easier to write VBA code to do the job.