Need Help Parsing Memo Field

[xom4life]

Hi All,
I need to convert a memo field to a 2 column database. Please see the
sample data and the desired results below to better understand my request.

Any and all help appreciated!!!!
xom4life

Sample Data:
Verse1
This is the song that never ends
It goes on and on my friend

Chorus1
Na Na Na Na
Na Na Na Na Na Na Na

Verse2
Oh well, It's time to end song
Aren't you glad?

Chorus2
Na Na Na Na
Na Na Na Na Na Na Na


Desired Result:
Section Lyrics
Verse1 This is the song that never ends
Verse1 It goes on and on my friend
Chorus1 Na Na Na Na
Chorus1 Na Na Na Na Na Na Na
Verse2 Oh well, It's time to end song
Verse2 Aren't you glad?
Chorus2 Na Na Na Na
Chorus2 Na Na Na Na Na Na Na

 

[Douglas J. Steele]

Is that 4 separate rows in the table, or is everything you're showing in the
memo field of a single row?

Are the hard line breaks actually in the data?

 

[xom4life]

Hi Douglas,
The sample data from above is 1 records memo field. The desired result
is multiple records as shown. The line breaks would be in the memo field.
I would be copying lyrics into this memo field and then converting them
to a database entry.

Thanks,
xom4life

Is that 4 separate rows in the table, or is everything you're showing in the
memo field of a single row?

Are the hard line breaks actually in the data?

Hi All,
I need to convert a memo field to a 2 column database. Please see the

[quoted text clipped - 30 lines]

Chorus2 Na Na Na Na
Chorus2 Na Na Na Na Na Na Na

 

[Douglas J. Steele]

You can use the Split function to break that text down into individual
lines:

Dim intLoop As Integer
Dim varLines As Variant

varLines = Split(MemoFieldText, vbCrLf)
For intLoop = LBound(varLines) To UBound(varLines)
Debug.Print "Line " & intLoop & ": " & varLines(intLoop)
Next intLoop

Once you've got that, you can loop through the array, finding the value for
the first column, getting the values for the second column and then writing
to the database.

Sorry, I'm going out of town for a week in a matter of minutes, so I can't
offer anything more concrete.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,
The sample data from above is 1 records memo field. The desired
result
is multiple records as shown. The line breaks would be in the memo field.
I would be copying lyrics into this memo field and then converting
them
to a database entry.

Thanks,
xom4life

Is that 4 separate rows in the table, or is everything you're showing in
the
memo field of a single row?

Are the hard line breaks actually in the data?

Hi All,
I need to convert a memo field to a 2 column database. Please see
the

[quoted text clipped - 30 lines]

Chorus2 Na Na Na Na
Chorus2 Na Na Na Na Na Na Na