Pull Last Name 3

[bernie10]

Hey everyone,
Suppose I have this sample data in the query MyQuery in the field FullName:

John Smith
Julie L. Jones
Josh Jacob Wright

I would like to create a new field to pull out the last name so I can sort by last name. Could somebody suggest the SQL that would do that.

Thanks,
Collen

 

[HarleyQuinn]

How about something like:

Surname: Right([Fullname],(Len([Fullname])-InStrRev([Fullname]," ")))

This code takes the right X characters where X is the length of the value - the position number of the last space.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

For your small sampling, and expression like:
LastName: Right([FullName],Len([FullName])-InStrRev([FullName]," "))
should work. You just need to hope no one has a space in their last name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bernie10]

Perfect, thanks.
Collen

 

[AlexCuse]

Hi Collen,

Sounds like you've got it sorted, but I think you might find this useful when working with FullName columns. I wrote the functions to deal with full names I had in the same format as yours (consider yourself lucky you don't have salutations and suffixes though ;-) )

faq705-6468

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[dhookom]

Alex,
Great code! thanks for sharing the link.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Anyway, why using Right and Len when a single Mid suffice ?
LastName: Mid([Fullname],1+InStrRev([Fullname]," "))

 

[AlexCuse]

Duane, that is high praise coming from you. Thanks!



Ignorance of certain subjects is a great part of wisdom

 

[dhookom]

Could update from PH. I started thinking Right() and was stuck.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlexCuse]

Curse you PH, now I will need to change that in my modules and update the FAQ's ;-)

Just kidding, have a star for saving me the extra function calls.

Ignorance of certain subjects is a great part of wisdom

 

[NYFashionToGo]

This works great, Thanks for the Info, I have a question on how to achieve something very similar, accept It is for a two parted First Name.... Such as Jo Ann or Beth Ann....

Or if there is a way to Remove the Last Name and be left with the rest. Because Not all first names are as this some are one word.....

Just curious... Very good post. Thanks

 

[PHV]

a way to Remove the Last Name
Left([Fullname],InStrRev([Fullname]," ")-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886