Extract part of a text

[Diem]

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

 

[CJBIRKIN]

Hi

Try this in a module.

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

 

[Diem]

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

 

[CJBIRKIN]

Hi, didn't think about that

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

 

[Diem]

Chris,

Very Grateful, thank you !!
Diem