Breaking memo field apart

[John]

Hi

I need to break a memo field apart by the individual lines of text it has. I
only need the last two lines. How do I do this in a query?

Thanks

Regards

 

[[MVP] S. Clark]

Probably can't be done, gracefully, in a query. Even VBA may be a stretch,
depending on the true definition of "last two lines"

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Vinson]

Hi

I need to break a memo field apart by the individual lines of text it has. I
only need the last two lines. How do I do this in a query?

Thanks

Regards

This strongly suggests that you're storing multiple "records" in a
memo field - which isn't how memo fields are designed to work! Might
you consider redesigning the tables so that each "line" is in a Text
field in its own record, in a many-side table?

That said, I agree with Steve Clark that you'll need some VBA to do
this. The InStrRev() function may let you find the last <Cr><Lf> pair,
but the next-to-last will get snarky!

 

[Brendan Reynolds \(MVP\)]

The following assumes that there will always be at least two lines. You'll
likely need to add a test for that, else you'll get an error if there is
only one line, or none.

If my memory is correct, I think you end up with a carriage-return/line-feed
combination if the user presses Enter alone to start a new line (works in a
text box with the multiline property set to true) but a single
carriage-return if the user presses Ctrl+Enter (as you have to to start a
new line if entering data directly into a table). Hence the use of the
Replace() function to ensure consistent line-endings.

I'm assuming Access 2000 or later - the Split() and Replace() functions were
both new in Access 2000, so this code will not work in Access 97.

Public Sub TestSub()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim astrWork() As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestMemo FROM tblTest")
Do Until rst.EOF
astrWork = Split(Replace(rst("TestMemo"), vbCrLf, vbCr), vbCr)
Debug.Print astrWork(UBound(astrWork) - 1),
astrWork(UBound(astrWork))
rst.MoveNext
Loop
rst.Clone
Set rst = Nothing
Set db = Nothing

End Sub

 

[Brendan Reynolds \(MVP\)]

Whoops! 'rst.Clone' should have been 'rst.Close' - sometimes 'Intellisence'
tries *too* hard to be helpful!

--
Brendan Reynolds (MVP)
(e-mail address removed)

rst.Clone

<snip>