Name Splitting

[Guest]

I am using Access 97 and I have a query that splits a name field into First, Middle, and Last Name. The name field is in the following format:

Smith, John J

The formulas I am using are as follows

Last Name: Left([NAME],InStr(1,[NAME],",")-1

First Name: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(1,[NAME]," ")

MI: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(InStr(1,[NAME]," ")+1,[NAME]," ")

The problems I am having are that the middle initial always shows in the First Name field and if the name has no middle initial then the whole name appears in the Middle Initial field. Here is an example

Name Last Name First Name Middle Initia
------ ------------- ------------- ----------------
Smith, John J. Smith John J. J

Doe, Jane Doe Jane Doe, Jan

Any help would be appreciated. Thank You

Dav

 

[Mark G. King]

I am using Access 97 and I have a query that splits a name field into

First, Middle, and Last Name. The name field is in the following format:

Smith, John J.
The formulas I am using are as follows:>
Last Name: Left([NAME],InStr(1,[NAME],",")-1)>
First Name: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(1,[NAME]," "))>
MI: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(InStr(1,[NAME]," ")+1,[NAME]," "))

The problems I am having are that the middle initial always shows in the

First Name field and if the name has no middle initial then the whole name
appears in the Middle Initial field. Here is an example:>

Name Last Name First Name Middle Initial
------ ------------- ------------- -----------------
Smith, John J. Smith John J. J.>
Doe, Jane Doe Jane Doe, Jane>
Any help would be appreciated. Thank You.
Dave

Hi Dave,

You might like to take a look at a third party utility for complex name
splitting needs like yours:
Try "Splitter for Microsoft Access" - it's an Access add-in that does
exactly what you want.
http://www.infoplan.com.au/splitter/

-Mark