Name Splitting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Dave said:
I am using Access 97 and I have a query that splits a name field into
Click to expand...
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
Click to expand...
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
Click to expand...

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
 
Back
Top