Name extraction SQL formula problem

Posted on 2008-06-19
Last Modified: 2011-10-19
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))
Question by:jaugermeister
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
LVL 22

Expert Comment

ID: 21826084
See if this works for you:

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

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21826377
try this

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

Author Comment

ID: 21829650
Capricorn1, that formula errors out if the middle initial is missing.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 21829657
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.

Author Comment

ID: 21829692
Flyster: this is a screen shot of the formula you provided.


Author Comment

ID: 21829696
Sorry forgot to add the picture

Author Comment

ID: 21829705
Capricorn1: This is a screen shot of the formula you provided.

Author Comment

ID: 21830286
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], ",")))
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 21830621
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))

Author Closing Comment

ID: 31468957
Thank GOD!

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question