Retrieving the last word of a field

[m001]

Is there a function that will retrieve the last 'word' typed in a field.
For example:

"My name is Anna" = "Anna"
"My father's age is 56" = "56"

The function 'rightwords' doesn't seem to exist in Access.

Thankyou for reading and eventually answering my question.

 

[Mile-O]

What version of Access are you using?

 

[m001]

Version

I'm using 'Access 2000'

 

[Mile-O]

I don't know if Access 200 has the InStrRev() function - it should do, though.

To get the last word (approximately)

=Right([MyTextbox], InStrRev(1, [MyTextbox], " "))

 

[m001]

Thanks but the InStrRev() function doesn't exist. :-(

Any other suggestions?

 

[Mile-O]

This code will reverse the string.

Public Function ReverseString(ByVal strTemp As String) As String
    Dim intCounter As Integer
    Dim strData As String
    For intCounter = 1 To Len(strTemp)
        strData = Mid(strTemp, intCounter, 1) & strData
    Next intCounter
    
    ReverseString = Trim(strData)
    
End Function

With the string reversed you can use the InStr() function to find the first space in the text and the Left() function to extract it. And then you reverse it again.

i.e.

Dim strPhrase As String, strResult As String
strPhrase = "My Name Is Anna"
strResult = ReverseString(strPhrase)
strResult = Left(strResult, InStr(1, strResult, " "))
strResult = ReverseString(strResult)

or:

Dim strPhrase As String, strResult As String
strPhrase = "My Name Is Anna"
strResult = ReverseString(Left(ReverseString(strPhrase), InStr(1, ReverseString(strPhrase), " ")))

 

[m001]

Pfoooh, I'm sure it works fine but I was looking for a simple calculation to insert in a query.

Anyway, thanks a lot!

 

[Oldsoftboss]

Sorry for the long reply that follows. Copy it to a module and browse through for instructions on how to use....

'* String Functions
'*
'* See the subroutine "examples" to test the supplied functions.
Option Compare Database
Option Explicit



**Edit**
Naaaah thats all BS

See the post below


Dave

 

[Oldsoftboss]

Sorry here it is in a Db

 

[Oldsoftboss]

And a version for Mile (Even though he probably wrote it in the first place)

Dave

 

[Mile-O]

I have XP now.

 

[Mile-O]

m001, the code I gave can eaily be used in a query.

i.e.

Public Function GetLastWord(ByVal strPhrase As String) As String
    GetLastWord = ReverseString(Left(ReverseString(strPhrase), InStr(1, ReverseString(strPhrase), " ")))
End Function

And also the ReverseString function

Put them both in a module.


Then in the query, you can define a new field:

NewField: GetLastWord([MyField])