Extract part of a text

Diem

New member
Local time
Today, 22:57
Joined
Jun 26, 2003
Messages
9
Hello All,

I know there are some threads that cover similar scenarios to what I'm about to ask but I still can't get my head around it. Hope someone can help.

Basically, I've got a memo field that has this standard structure.

xx/xx/xx - nnnnnnnnn etc.

(two carriage returns)
xx/xx/xx - nnnnnnnnnn etc.

(two carriage returns)
xx/xx/xx - nnnnnnnnnn etc.

And so on...

Where xx/xx/xx is a date and nnnnnnnn is just text.

I want to be able to extract the last instance of
xx/xx/xx - nnnnnnnnnn etc.

I tried various combinations of the Mid, Right, InStr functions but still can't get it right (??!!)

Please help!!

Thanks
Dm
 
Hi

Try this in a module.

PHP:
Public Function GetLastSentence(StrMemo As String)
Dim StrNew As String
Dim IntStrPos As Integer


IntStrPos = 1

Do Until IntStrPos = 0

    IntStrPos = InStr(IntStrPos, StrMemo, Chr(10))
    
    If IntStrPos <> 0 Then
        StrNew = Mid(StrMemo, IntStrPos + 1) ' You might not need the + 1 bit
        StrMemo = StrNew
    End If
    
Loop
GetLastSentence = StrNew

End Function


Simply put this in a query

Test: GetLastSentence([Name of your Memo field goes here])

Hope that helps

Chris
 
Hi Chris,

Thank you so much!!. It's working great but what if the memo field has only one instance of this:

xx/xx/xx - nnnnnnnnn etc.

The function doesn't return it(?)
Dm
 
Hi, didn't think about that:D


PHP:
Public Function GetLastSentence(StrMemo As String)
Dim StrNew As String
Dim IntStrPos As Integer


IntStrPos = 1

' ADD THIS

' THIS PART DETERMINES IF THERE IS A RETURN CHARACTER
If InStr(IntStrPos, StrMemo, Chr(10)) = 0 then

'IF THERE IS NO RETURN CHARACTER THEN THERE IS EITHER ONLY
'ONE RECORD OR NO DATA

'THE NZ FUNCTION WILL RETURN "NO DATA" IF StrMemo IS EMPTY

        GetLastSentence = NZ(StrMemo,"NO DATA") 

Else


      Do Until IntStrPos = 0

             IntStrPos = InStr(IntStrPos, StrMemo, Chr(10))
    
                  If IntStrPos <> 0 Then
                      StrNew = Mid(StrMemo, IntStrPos + 1) ' You might not need the + 1 bit
                           StrMemo = StrNew
                   End If
    
        Loop
        
         GetLastSentence = StrNew

End If


End Function


Chris
 

Users who are viewing this thread

Back
Top Bottom