By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

How to return the last word in a string

P: 3
My Employees table contains staffs' full names as it is an import from the HR system and I don't have control over the format.
Many of our staff have names 4, 5 or even 6 words long. I wish to use a combobox based on a query that is based on the Employees table that allows me to sort on the last name.
Currently the Names field consists of first_name second-fourth_names last_name
eg Jane Elizabeth Francis Jones
John Doe
Abdullah Mohammed Ouzi Nasser Shaban

I have found a query function
LastName: IIf(InStr(InStr([Name]," ")+1,[Name]," ")<>0,Right([Name],Len([Name])-InStr(InStr([Name]," ")+1,[Name]," ")),Right([Name],Len([Name])-InStr([Name]," ")))

Unfortunately in the example above it returns
Francis Jones, not Jones
Ouzi Nasser Shaban, not Shaban

My skills are limited and I don't know how to edit this function to parse right to the end of the string and pick up the last name only.

Can somebody please help.

Dec 7 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 2,653
Hi, sfindlay.

Try one of this:
Expand|Select|Wrap|Line Numbers
  1. Mid([Name], InStrRev([Name], " ") + 1)
  2. Split([Name], " ")(UBound(Split([Name], " ")))
The first is expected to be faster.

Dec 7 '07 #2

Expert Mod 15k+
P: 31,656
Nice one Fish. Another one to add to those I was ignorant of :)

SFindlay, You need to replace your line with :
Expand|Select|Wrap|Line Numbers
  1. LastName: Mid([Name], InStrRev([Name], " ") + 1)
Let us know what this gives you :)
Dec 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.