Need Help Parsing Memo Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xom4life - 09 Mar 2007 17:08 GMT

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

Reply to this Message

Douglas J. Steele - 09 Mar 2007 20:58 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> 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

Reply to this Message

xom4life - 09 Mar 2007 22:34 GMT

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?
[quoted text clipped - 6 lines]
>> Chorus2 Na Na Na Na
>> Chorus2 Na Na Na Na Na Na Na

Reply to this Message

Douglas J. Steele - 10 Mar 2007 11:06 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Douglas,
> The sample data from above is 1 records memo field. The desired
[quoted text clipped - 19 lines]
>>> Chorus2 Na Na Na Na
>>> Chorus2 Na Na Na Na Na Na Na

Reply to this Message