Last line of text from a memo field

[kleaves]

Can anyone help....

I have a field on an Access db, this is a "Memo " type field which has many lines of text within it.

I need to run a query on this to get the last line of text within this field.

Aside from records where this memo field is filled in I also have records where the memo field is blank. So basically if the memo field is blank I want a blank field returned as well as fields with data.

I am using Access 2003 but also am running this on Access 2000 (not sure if there has been a change to function names)

This is seriously bugging me - can anyone put me out of my misery?

 

[pdx_man]

Ah string manipulation. One of my favs. But, as you can see from my example, do you realy want. The last line or the last sentence. Some people define this as the same, so I will include both here.
I have a table named Tbl_Mem with a memo field named MemField. Some have hard breaks, others don't.

SELECT Tbl_Mem.Field1, Tbl_Mem.MemField, IIf([MemField] Is Null,Null,(Right([MemField],Len([MemField])-InStrRev([MemField],Chr(13))-1))) AS LastLine, IIf([MemField] Is Null,Null,Right([MemField],Len([MemField])-InStrRev([MemField],'.',Len([MemField])-2)-1)) AS LastSentence
FROM Tbl_Mem;

 

[kleaves]

pdx_man

Thanks for that - this works a treat on Access 2003. Just another question though, is this possible on Access 97? The function InstrRev is not available in Access 97, How would I achieve the same result in this older version as well?

 

[Keith Nichols]

Just a thought now that your problem is solved.

If you need this data separately, why don't you have a dedicated memo field for whatever is placed in the last line/sentence?

This would give you plenty of flexibility to use it separately or concatenate it with the rest of the memo if needed.

Regards,

 

[raskew]

Hi -

Try this:

Function InStrRev97(ByVal pstr As String, pItem As String) As Integer
'*******************************************
'Purpose:   Return location of last instance of a character or phrase.
'Inputs:    ? InStrRev97("the quick brown fox jumped the lazy dog", "the")
'Output:    28 - Location of last occurence of "the"
'*******************************************

Dim i    As Integer
Dim n    As Integer
Dim tLen As Integer

    n = 0
    tLen = Len(pItem)
    For i = Len(RTrim(pstr)) To 1 Step -1
      If Mid(pstr, i, tLen) = pItem Then
          n = i
          Exit For
      End If
    Next i

    InStrRev97 = n

End Function

HTH - Bob

 

[kleaves]

Last LIne of text from a memo

Thanks to everyone who replied so quickly to this thread !

After much testing I have decided the easiest thing to do is to upgrade my other Access database from Access 97 to 2003 and use the select statement as offered by pbx_man.

Thanks again to all your suggestions.

much appreciated !!

Kleaves