Parse First Name

[sombiatz]

Hi All,

I am trying to parse out a full name field into a First Name, Last Name, and Middle Initial field. The data is currently formatted as follows:

Name
MOORE,RICHARD
TAYLOR,MARTIN A
BROWN,SUSAN A
ROCKWELL,BRITTNEY E
LORD-PAGE,TREVOR

I am able to successfully parse out the last name and the middle initial with the following.



MiddleInitial: Mid([BarProceduresInsuranceQueryC.Name],InStr([BarProceduresInsuranceQueryC.Name] & " "," ")+1,1)
LastName: Left([BarProceduresInsuranceQueryC.Name],InStr(1,[BarProceduresInsuranceQueryC.Name],",")-1)

I am having trouble with the first name because I don't know how to stop grabbing the middle initial. I currently have this:

FirstName: Mid([dbo_BarVisits.Name],InStr(1,[dbo_BarVisits.Name],",")+1)

But, with the name examples above, it is giving me the following:

RICHARD
MARTIN A
SUSAN A
BRITTNEY E
TREVOR

How do I change it so that it stops pulling the middle initial?

Thanks!

[ranman256]

if the left char of the right 2 chars is a space then
it is an initial so skip the last char
else
use the whole word

iif(left(right([dbo_BarVisits.Name],2),1) = " " ,Mid([dbo_BarVisits.Name],InStr(1,[dbo_BarVisits.Name],",")+1, len(Mid([dbo_BarVisits.Name],InStr(1,[dbo_BarVisits.Name],",")+1)-1), Mid([dbo_BarVisits.Name],InStr(1,[dbo_BarVisits.Name],",")+1))