Solved

Name extraction SQL formula problem

Posted on 2008-06-19
10
225 Views
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))
0
Comment
Question by:jaugermeister
  • 7
  • 2
10 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 21826084
See if this works for you:

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

Flyster
0
 
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], ",")))
0
 

Author Comment

by:jaugermeister
ID: 21829650
Capricorn1, that formula errors out if the middle initial is missing.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:jaugermeister
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.
0
 

Author Comment

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

0
 

Author Comment

by:jaugermeister
ID: 21829696
Sorry forgot to add the picture
First-Name-Only-1.bmp
0
 

Author Comment

by:jaugermeister
ID: 21829705
Capricorn1: This is a screen shot of the formula you provided.
First-Name-Only-2.bmp
0
 

Author Comment

by:jaugermeister
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], ",")))
0
 
LVL 120

Accepted Solution

by:
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))
0
 

Author Closing Comment

by:jaugermeister
ID: 31468957
Thank GOD!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

786 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