Splitting Names

[FrankM]

I have a Table with names. The names could have middle name or initals or
none. I need a Query to extract just the last name. I think this should be
fairly simple but I can't figure it out.

Examples of names:
John Smith
John T Smith
John Jacob Smith
John T Jacob Smith

For each of the examples above I need a Query just to grab the Smith part.

I started with this ...
LastName: Right([Name],Len([Name])-InStr(1,[Name]," "))

But it only removed the first name so "John T Smith" appeared as "T Smith";
when I really need just "Smith". I have a feeling I'm missing something
really simple.

Any assistance would be greatly appreciated.

 

[fredg]

I have a Table with names. The names could have middle name or initals or
none. I need a Query to extract just the last name. I think this should be
fairly simple but I can't figure it out.

Examples of names:
John Smith
John T Smith
John Jacob Smith
John T Jacob Smith

For each of the examples above I need a Query just to grab the Smith part.

I started with this ...
LastName: Right([Name],Len([Name])-InStr(1,[Name]," "))

But it only removed the first name so "John T Smith" appeared as "T Smith";
when I really need just "Smith". I have a feeling I'm missing something
really simple.

Any assistance would be greatly appreciated.

Access 2000 or newer?

LastName:Mid([FullName,InStrRev([FullName," ")+1)

Note: this will return the last name if the last name is a single
word, i.e. "Smith" or "Pasternak", etc., but will not be correct if
the last name is not a single word, i.e. "van den Steen" or "von
Kruger".

Your data structure is not correct. You should have separate fields
for the First Name, Middle Name (or initial), and Last Name.

 

[FrankM]

That was perfect, did exactly what I needed. Thank you.

For the record I'm using Access 2003, I know I should have included that, my
mistake. I also realise that the database structure leaves something to be
desired but I did not create it. Sometimes things are beyond our control.

Thank you!

I have a Table with names. The names could have middle name or initals or
none. I need a Query to extract just the last name. I think this should be
fairly simple but I can't figure it out.

Examples of names:
John Smith
John T Smith
John Jacob Smith
John T Jacob Smith

For each of the examples above I need a Query just to grab the Smith part.

I started with this ...
LastName: Right([Name],Len([Name])-InStr(1,[Name]," "))

But it only removed the first name so "John T Smith" appeared as "T Smith";
when I really need just "Smith". I have a feeling I'm missing something
really simple.

Any assistance would be greatly appreciated.

Access 2000 or newer?

LastName:Mid([FullName,InStrRev([FullName," ")+1)

Note: this will return the last name if the last name is a single
word, i.e. "Smith" or "Pasternak", etc., but will not be correct if
the last name is not a single word, i.e. "van den Steen" or "von
Kruger".

Your data structure is not correct. You should have separate fields
for the First Name, Middle Name (or initial), and Last Name.