?
Solved

Name extraction SQL formula problem

Posted on 2008-06-19
10
Medium Priority
?
230 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
[X]
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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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