Seperate First Name and Last Name

  • Thread starter Thread starter Jeanne S
  • Start date Start date
J

Jeanne S

I have imported data into a database that has the Last Name, First Name M in
one field and I want to seperate them in a query. I have used the following
for the Last Name and that works but I can't figure out how to get the First
Name with No Middle Initial. Please help.

Last Name: Left([Emp Name],InStr(1,[Emp Name],",")-1)
 
I have imported data into a database that has the Last Name, First Name Min
one field and I want to seperate them in a query.  I have used the following
for the Last Name and that works but I can't figure out how to get the First
Name with No Middle Initial.  Please help.

Last Name: Left([Emp Name],InStr(1,[Emp Name],",")-1)
Click to expand...

Try mid(Left([Emp Name],InStr(1,[Emp Name],",")),[Emp Name],",") for
the first name.
and right([Emp Name],1) for the Initial.

I believe the above mid would actually give you the middle initial as
well.
not sure if this would work.. but it could be...
Left(mid(Left([Emp Name],InStr(1,[Emp Name],",")),[Emp Name],","),
Len(mid(Left([Emp Name],InStr(1,[Emp Name],",")),[Emp Name],",")) -2)
 
The solution depends on if your field always has the format
Last Name, Comma, First Name, Space, Middle initial

First Name with MI: Mid([Emp Name],InStr(1,[Emp Name],",")+1)

First Name without MI:
LEFT(Mid([Emp Name],InStr(1,[Emp Name],",")+1),
Len(Mid([Emp Name],InStr(1,[Emp Name],",")+1))-2)

IF your name pattern isn't exactly as you have described, then you are
likely to have a problem.

What about
Spencer, Jr, John P
or
Spencer, John P Jr.
???




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
On Wed, 22 Oct 2008 13:38:01 -0700, Jeanne S <Jeanne
I have imported data into a database that has the Last Name, First Name M in
one field and I want to seperate them in a query. I have used the following
for the Last Name and that works but I can't figure out how to get the First
Name with No Middle Initial. Please help.

Last Name: Left([Emp Name],InStr(1,[Emp Name],",")-1)
Click to expand...

What do you want to do with first names like "Norma Jean" or "Billy Bob"? They
do exist...

To get just the text between the comma and the next blank, try

FirstName: Mid([Emp Name], InStr([Emp Name], ", ") + 2, Instr(InStr([Emp
Name], ", ") + 2, [Emp Name], " ") - InStr([Emp Name], ", ") - 2)
 
Back
Top