# 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))
###### Who is Participating?

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

Commented:
See if this works for you:

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

Flyster
0

Commented:
try this

FirstName:Trim(Mid([PATIENT], InStr([PATIENT], ",") + 1, InStr(InStr([PATIENT], ",") + 2, [PATIENT], " ") - InStr([PATIENT], ",")))
0

Author Commented:
Capricorn1, that formula errors out if the middle initial is missing.
0

Author 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

Author Commented:
Flyster: this is a screen shot of the formula you provided.

0

Author Commented:
Sorry forgot to add the picture
First-Name-Only-1.bmp
0

Author Commented:
Capricorn1: This is a screen shot of the formula you provided.
First-Name-Only-2.bmp
0

Author 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

Author 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.