Extracting the Last Word of a String

[Ozzie via AccessMonster.com]

I have a problem in that I am trying to write a query that extracts the last
word from a table field, by looking for the last space character.

I can do this in excel quite easily by using the Len, Find and Substitute
functions, however I do not have the Substitute or Find function in Access?

I have searched various posts but can't seem to find what I need.

If anyone can help it would be most appreciated,

Cheers

 

[John Spencer]

What version of Access are you using?

IF later versions you could use
Trim(Mid("This is the last word",InstrRev("This is the last word","
",-1,1)))

If Access2000, you will probably need to write a vba function to use
InStrRev since the Expression service is not aware of the InStrRev function
in VBA. Actually, I would probably write the function anyway. UNTESTED
AIRCODE

Function fGetLastWord (strIN)
if Len(strIN & vbnullstring) = 0 Then
fGetLastWord = strIn
ElseIf Instr(Trim(strIn)," ") = 0 then
fGetLastWord = Trim(strIN)
Else
strIn= Trim(strIn)
fGetLastWord = Trim(Mid(StrIn,InstrRev(StrIn," ",-1,1)))
End If

End function

 

[Marshall Barton]

I have a problem in that I am trying to write a query that extracts the last
word from a table field, by looking for the last space character.

I can do this in excel quite easily by using the Len, Find and Substitute
functions, however I do not have the Substitute or Find function in Access?

I have searched various posts but can't seem to find what I need.

In AXP and later:

Mid(field, InStrRev(field, " ") + 1)

 

[Ozzie via AccessMonster.com]

Thanks for your replies, I was using 2003, I've applied your code and it
works a treat,

many thanks

I have a problem in that I am trying to write a query that extracts the last
word from a table field, by looking for the last space character.

[quoted text clipped - 3 lines]

I have searched various posts but can't seem to find what I need.

In AXP and later:

Mid(field, InStrRev(field, " ") + 1)