How to extract parts of a field

[Guest]

I want do extract first and last name of employee to seperate fields from a
combined name field e.g John Smith I have no problem when just two name but
with more I do. e.g need Mary Jones from Mary Jane Sarah Jones. Please Help

 

[Rick B]

There is no reliable way to do this. Fix the ones you can with an update
query, then go back and clean it up.

The reason you can't reliable do it is...

Billy Ray Cyrus (First two words go in first)
Billy Mac Arthur (Last two words go in last)



--
Rick B

I want do extract first and last name of employee to seperate fields from a
combined name field e.g John Smith I have no problem when just two name but
with more I do. e.g need Mary Jones from Mary Jane Sarah Jones. Please

Help

 

[[MVP] S.Clark]

Left$("Steve Clark",5) = "Steve"
Right$("Steve Clark",5) = "Clark"
Mid$("Steve Clark",6) = "Clark"
Instr("Steve Clark", " ") = 5

Left$("Lawrence Unknown", Instr("Lawrence Unknown", " ")-1) = "Lawrence"
Mid$("Lawrence Unknown", Instr("Lawrence Unknown", " ")+1) = "Unknown"

This all works when there is a space seperating the first and last name.
Extra provisions may be needed for Middle names and/or intiials

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Guest]

Thanks Rick give me a solution and saved me 'chasing my tail'

 

[Guest]

Thanks Steve works well with two names but I have problems with determing
correct additional provisons to handles additonal names.