How to extract LastName, FirstName, MI? (Not all with MI). Sample
names:

John Doe
John M Doe

Thanks

Unfortunately, there's no simple answer. Sometimes the first name is two 
parts and sometimes the last name is (Mary Lou Retton, Ludwig von 
Beethoven). Sometimes there's only a first name. (Pele, Cher)

Realistically, you're likely going to USB (use someone's brain)

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Song" <song.usa@gmail.com> wrote in message 
news:6a718e9d-9acd-49ec-ba5e-24baf0bb08d7@k36g2000prb.googlegroups.com...
> How to extract LastName, FirstName, MI? (Not all with MI). Sample
> names:
>
> John Doe
> John M Doe
>
> Thanks 

See:
    http://allenbrowne.com/func-10.html

To get the first word from YourField, use:
    ParseWord([YourField], 1)

To get the last word, use:
    ParseWord([YourField], -1)

The middle name won't be as easy: Instr(), Mid(), and Len() may help.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Song" <song.usa@gmail.com> wrote in message 
news:6a718e9d-9acd-49ec-ba5e-24baf0bb08d7@k36g2000prb.googlegroups.com...
> How to extract LastName, FirstName, MI? (Not all with MI). Sample
> names:
>
> John Doe
> John M Doe
>
> Thanks 

On 24.03.2010 14:33, Song wrote:
> How to extract LastName, FirstName, MI? (Not all with MI).

http://support.microsoft.com/kb/168799

Caveat: Every known way to parse a string into names is error prone.

http://stackoverflow.com/questions/103422/simple-way-to-parse-a-persons-name-into-its-component-parts

There are two basic problems:

1) You cannot rely on order. You have always a cause for distrust on 
user input.

2) There are weird names out there in the wild. Especially there are 
surnames consisting of two or more words only separated by spaces.

So in short: It's a NP-hard task... okay not really, you only need a 
really large rainbow table with approximately 6 billion names. With 
reduction it will be smaller than that but still large enough ;)


mfG
--> stefan <--

As pointed out by others, not a simple task.  And I'll add folks who add ", 
Jr.", ", Senior", and ", III" to their names.

You can try using Left(), Right(), Mid(), and Instr() functions to start 
with, then perform a final validation using someone's brain.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Song" <song.usa@gmail.com> wrote in message 
news:6a718e9d-9acd-49ec-ba5e-24baf0bb08d7@k36g2000prb.googlegroups.com...
> How to extract LastName, FirstName, MI? (Not all with MI). Sample
> names:
>
> John Doe
> John M Doe
>
> Thanks