I have a database field that represents an item description and varying
length. I am using the database to import the item description into another
program (the other program is ODBC-aware ). The problem is that the
description would be imported into the other program in lengths of 50
characters. So if the entire description is 160 chars, the program would
need the description in 4 rows - 3 rows of 50 and 1 row of 10.

I have been trying to determine a way to write a query that would
essentially parse the description field into lengths of 50 chars AND return
each parsed chunk as a different row. Needless to say, I am stumped.

If anyone has any ideas or suggestions, it would be greatly appreciated!

thanks in advance,
bill

Look up union queries. That will allow you do what you seek:

SELECT Left([FieldName], 50) AS ParsedValue
FROM TableName
UNION ALL
SELECT Mid([FieldName], 51, 50)
FROM TableName AS T2
UNION ALL
SELECT Mid([FieldName], 101, 50)
FROM TableName AS T3
UNION ALL
SELECT Mid([FieldName], 151)
FROM TableName AS T3;
--

Ken Snell
<MS ACCESS MVP>



"bu" <bu (AT) nospam (DOT) com> wrote

Thanks for the suggestion, but I don't think that would be dynamic enough
because the length of the description could always change - sometimes it may
be 101 characters, others may be 234, etc. I would have to dynamically
create a select statement with "n" number of unions...


"Ken Snell" <kthsneisllis9 (AT) ncoomcastt (DOT) renaetl> wrote

The union query could be created in VBA code and then executed by that same
code. You'd have to have your code get the total length, decide how many
records will be needed, and then build the SQL statement accordingly. Not an
overly complex thing to do, if you can use code in this situation. Will your
situation be amenable to using code? Post back if you need more help.
--

Ken Snell
<MS ACCESS MVP>



"bu" <bu (AT) nospam (DOT) com> wrote