query to split fields into multiple rows?

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

re: query to split fields into multiple rows?

bu wrote:[color=blue]
> 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
>
>[/color]

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.

re: query to split fields into multiple rows?

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" <bu@nospam.com> wrote in message
news:8jUJd.5606$pS3.2017@fe07.lga...[color=blue]
>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
>
>[/color]

re: query to split fields into multiple rows?

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

Thanks!


"Salad" <oil@vinegar.com> wrote in message
news:hGXJd.7173$r27.6905@newsread1.news.pas.earthl ink.net...[color=blue]
> bu wrote:[color=green]
> > I have a database field that represents an item description and varying
> > length. I am using the database to import the item description into[/color][/color]
another[color=blue][color=green]
> > 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[/color][/color]
would[color=blue][color=green]
> > 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[/color][/color]
return[color=blue][color=green]
> > 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
> >
> >[/color]
>
> 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.[/color]

re: query to split fields into multiple rows?

This is also a good idea.... thanks for the help!


"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:41f84864$0$25498$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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" <bu@nospam.com> wrote in message
> news:8jUJd.5606$pS3.2017@fe07.lga...[color=green]
> >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[/color][/color]
would[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

re: query to split fields into multiple rows?

bu wrote:[color=blue]
> This is also a good idea.... thanks for the help![/color]

I vote for Allen's solution. Very easy to implement.
[color=blue]
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:41f84864$0$25498$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>[color=green]
>>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" <bu@nospam.com> wrote in message
>>news:8jUJd.5606$pS3.2017@fe07.lga...
>>[color=darkred]
>>>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[/color][/color]
>
> would
>[color=green][color=darkred]
>>>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
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]