Extracting the Last Word of a String

  • Thread starter Ozzie via AccessMonster.com
  • Start date
O

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
 
J

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
 
M

Marshall Barton

Ozzie said:
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.
Click to expand...


In AXP and later:

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

Ozzie via AccessMonster.com

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

many thanks




Marshall said:
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.
Click to expand...
[quoted text clipped - 3 lines]
I have searched various posts but can't seem to find what I need.
Click to expand...

In AXP and later:

Mid(field, InStrRev(field, " ") + 1)
Click to expand...
 
Top