Splitting A Name

[ECEK]

How can I extract everything Right of the first space.

I am trying to extract the First Name and Second Name from a field. My problem arises when a name is double spaced.

John Smith = John and Smith
Peter De Lane - Peter and De Lane

thanks peoples

 

[ECEK]

Found a solution:
Trim(Replace([name],Left([name],InStr([name]," ")-1),""))

 

[isladogs]

Ah but what about names like Anne Marie Smith?

 

[ECEK]

My list is only first name and second name. You have (I assume) introduced a middle name. That is indeed a different question.

 

[isladogs]

If what you've got now works that's fine but I was speaking from experience of unusual names.

For example, some people have names like Anne Marie without a hyphen and both are expected to be the first name.

Similarly many people have double barrelled surnames without a hyphen
E.g Fred Smith Jones where Smith is part of the surname

One of the most confusing examples I can remember from teaching was a student whose first name was C J and it wasn't short for anything.

 

[The_Doc_Man]

The one that always got me was the military way of indicating that someone had no middle name or initial. You know those government documents that have pages that say "This page intentionally left blank" (so of course it isn't blank - it has a disclaimer)? The U.S. military uses nmn ("no middle name") as the middle name.

 

[jdraw]

Just a comment for ECEK:

In your analysis of the "problem/opportunity", be sure to ask those questions along what ridders has shown.
eg:
are we dealing with first name and last name?
Is it possible to have a middle name or initial?
are unhyphenated compound first names (anne marie, jean paul, marc andre..) in scope?

Quite often people/users/bosses tend to give simple examples, but you have to look beyond the simple to get clarification. This isn't new or extreme, just based on some experience.

As per many posts, get the specs documented, clarified and confirmed before design.

Good luck.