query to split fields into multiple rows?

[bu]

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

[Salad]

bu wrote:

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

Hmmm...of the top of my head...

Write a function that uses the InsertInto SQL command. In this
function, you'd pass the string to be split and any "key" info. Here's
some air code

Public Sub SplitIT(strToSplit As String)
Dim intLoop As Integer
Dim strHold As String
strInsert As String

strToSplit = Trim(strToSpit)

intLoop = 50
Do while intLoop - 49 >= len(strToSplit)
strHold = Mid(strToSplit,intLoop -49,50)
'get the 50 chars and insert into the export table
strInsert = "Insert into ExportTable " & _
"(Field) Values ('" & strHold & "')"
Currentdb.Execute strInsert
intLoop = intLoop + 50
Loop
End Sub
I'd have a table that I'd export to your other program. Then run a query
to clear out all existing records in the export table. Then open a
recordset for the records you'll be exporting. Then loop thru the
recordset and pass the description field (and any others to insert into
export) and insert those records to the export file.

[Allen Browne]

Execute a series of Append query statements, reading 50-char at a time.
For example, the source for the 2nd one would be:
SELECT Mid([Descrip], 51, 50)
FROM Table1
WHERE Len([Descrip]) > 50

If this is extended, or needs to be done repeatedly, you could write some
VBA code that loops through appending each 50 char at a time, until there
are no more records returned, i.e. until db.RecordsAffected = 0.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bu" <[e-mail]> wrote in message
news:8j*****************@fe07.lga...

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

[bu]

I think I like this solution... I'll give it a whirl!

Thanks!
"Salad" <oi*@vinegar.com> wrote in message
news:hG*****************@newsread1.news.pas.earthl ink.net...

bu wrote:

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

Hmmm...of the top of my head...

Write a function that uses the InsertInto SQL command. In this
function, you'd pass the string to be split and any "key" info. Here's
some air code

Public Sub SplitIT(strToSplit As String)
Dim intLoop As Integer
Dim strHold As String
strInsert As String

strToSplit = Trim(strToSpit)

intLoop = 50
Do while intLoop - 49 >= len(strToSplit)
strHold = Mid(strToSplit,intLoop -49,50)
'get the 50 chars and insert into the export table
strInsert = "Insert into ExportTable " & _
"(Field) Values ('" & strHold & "')"
Currentdb.Execute strInsert
intLoop = intLoop + 50
Loop
End Sub
I'd have a table that I'd export to your other program. Then run a query
to clear out all existing records in the export table. Then open a
recordset for the records you'll be exporting. Then loop thru the
recordset and pass the description field (and any others to insert into
export) and insert those records to the export file.

[bu]

This is also a good idea.... thanks for the help!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Execute a series of Append query statements, reading 50-char at a time.
For example, the source for the 2nd one would be:
SELECT Mid([Descrip], 51, 50)
FROM Table1
WHERE Len([Descrip]) > 50

If this is extended, or needs to be done repeatedly, you could write some
VBA code that loops through appending each 50 char at a time, until there
are no more records returned, i.e. until db.RecordsAffected = 0.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bu" <[e-mail]> wrote in message
news:8j*****************@fe07.lga...

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

[Salad]

bu wrote:

This is also a good idea.... thanks for the help!
I vote for Allen's solution. Very easy to implement.


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Execute a series of Append query statements, reading 50-char at a time.
For example, the source for the 2nd one would be:
SELECT Mid([Descrip], 51, 50)
FROM Table1
WHERE Len([Descrip]) > 50

If this is extended, or needs to be done repeatedly, you could write some
VBA code that loops through appending each 50 char at a time, until there
are no more records returned, i.e. until db.RecordsAffected = 0.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bu" <[e-mail]> wrote in message
news:8j*****************@fe07.lga...

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