Name extraction SQL formula problem

Need short term fix...
Need this formula to extract only the first name ignoring the middle initial and suffix.

The Patient Field lists the order of the names as such:

Doe, John M Jr
Doe, John M
Doe, John

The formula I have now hiccups on the first name extraction because of either the middle initial is NOT in the name or when the middle name has more than one word (eg: Doe, John Young Lee) I need just the first name EXCLUDING the middle initial (or full middle) in the output.

This is the existing formula:
First: Trim(Left(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")-1))
jaugermeisterAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

FirstName: IIf(InStr(Trim([Patient])," ")>0,Trim(Mid([PATIENT],InStr([PATIENT],",")+1,InStr(InStr([PATIENT],",")+2,[PATIENT]," ")-InStr([PATIENT],","))),Mid([patient],InStr([patient],",")+1))
0
 
FlysterCommented:
See if this works for you:

Mid([Patient],InStr([Patient],",")+2,InStr([Patient]," "))

Flyster
0
 
Rey Obrero (Capricorn1)Commented:
try this


FirstName:Trim(Mid([PATIENT], InStr([PATIENT], ",") + 1, InStr(InStr([PATIENT], ",") + 2, [PATIENT], " ") - InStr([PATIENT], ",")))
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
jaugermeisterAuthor Commented:
Capricorn1, that formula errors out if the middle initial is missing.
0
 
jaugermeisterAuthor Commented:
Flyster, the formula is giving me the middle initial which is okay however if the persons name does not contain a middle initial the formula displays nothing in the field.
0
 
jaugermeisterAuthor Commented:
Flyster: this is a screen shot of the formula you provided.

0
 
jaugermeisterAuthor Commented:
Sorry forgot to add the picture
First-Name-Only-1.bmp
0
 
jaugermeisterAuthor Commented:
Capricorn1: This is a screen shot of the formula you provided.
First-Name-Only-2.bmp
0
 
jaugermeisterAuthor Commented:
is the way way you can use a IIF, THEN statement (the formula below) to resolve the issue of a persons name NOT having a middle initial? This formula works like a charm for exception of when a person has NO middle initial the formula errors out... I'm going crazy...

FirstName:Trim(Mid([PATIENT], InStr([PATIENT], ",") + 1, InStr(InStr([PATIENT], ",") + 2, [PATIENT], " ") - InStr([PATIENT], ",")))
0
 
jaugermeisterAuthor Commented:
Thank GOD!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.