split names in into 2 fields in access

[Guest]

I have a name field that has the complete name in it. I need to seperate it
into a FirstName and LastName fields. The names are entered with space
between names, in the following format: firstname lastname. The problem I am
having is when the first name comprises of two names . Example mary kate
olsen. I can't figure out how to put "mary kate" into the firstname field.
I end up with
FirstName lastName
Mary kate olsen

I tring this in an update query using the following
LastName:
LTrim(Right$([TransPatient],Len([TransPatient])-InStr([TransPatient]," ")+1))

FirstName: Left([transpatient],InStr([transpatient]," ")-1)

I am using Access 2003.

Thank you for your help,
Harry

 

[fredg]

I have a name field that has the complete name in it. I need to seperate it
into a FirstName and LastName fields. The names are entered with space
between names, in the following format: firstname lastname. The problem I am
having is when the first name comprises of two names . Example mary kate
olsen. I can't figure out how to put "mary kate" into the firstname field.
I end up with
FirstName lastName
Mary kate olsen

I tring this in an update query using the following
LastName:
LTrim(Right$([TransPatient],Len([TransPatient])-InStr([TransPatient]," ")+1))

FirstName: Left([transpatient],InStr([transpatient]," ")-1)

I am using Access 2003.

Thank you for your help,
Harry

You're going to have a problem now matter how you do it.
If the names were simply like John Smith, it would be easy enough:

FirstName:Left([FullName],InStr([FullName]," ")-1)
LastName:Mid([FullName],InStr([FullName]," ")+1)

However, as you have discovered, many people use 2 first names, John
David Smith. Others have more than one space in their Last Name. Jan
van den Steen.

One alternative is to manually go through your records and replace the
space between first and last name with a comma, i.e. John David,Smith
or Jan,van den Steen.

Then use
FirstName:Left([FullName],InStr([FullName],",")-1)
LastName:Mid([FullName],InStr([FullName],",")+1)

 

[Douglas J. Steele]

From my archives:

Name splitter: http://www.infoplan.com.au/splitter/
Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html
There's also a KB article about this at
http://support.microsoft.com/?kbid=168799