How to return the last word in a string

[sfindlay]

Hi
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
Doe
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.

Thanks

[FishVal]

Hi, sfindlay.

Try one of this:

Mid([Name], InStrRev([Name], " ") + 1)
Split([Name], " ")(UBound(Split([Name], " ")))
 

The first is expected to be faster.

Regards,
Fish

[NeoPa]

Nice one Fish. Another one to add to those I was ignorant of :)

SFindlay, You need to replace your line with :

LastName: Mid([Name], InStrRev([Name], " ") + 1)

Let us know what this gives you :)