Try simplifying the expression and then adding in a bit at a time.
FirstNameAndInitial: IIF([PROV NM] Like "*, DO"
or [PROV NAME] Like "*, MD",
Mid([PROV NM], Instr([PROV NM],",")+2)
, Null)
If that works then add some more to it.
FirstNameAndInitial: IIF([PROV NM] Like "*, DO"
or [PROV NAME] Like "*, MD"
, Mid([PROV NM], Instr([PROV NM],",")+2, Len([PROV NM]) -5
-Instr([PROV NM],",")), Null)
FirstNameAndInitial: TRIM(IIF([PROV NM] Like "*, DO"
or [PROV NAME] Like "*, MD"
, Mid([PROV NM], Instr([PROV NM],",")+2, Len([PROV NM]) -5
-Instr([PROV NM],",")), Null))
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you for your help. However, the "FirstName and Initial" is giving me
an error message I tried several things and I am still getting the following
message: "The expression you entered has a function containing the wrong
number of arguments".
Can you please help me to find what is wrong? Thank you again.
FirstNameAndInitial: IIF([PROV NM] Like "*, DO"or [PROV NAME] Like "*,
MD",TRIM(Mid([PROV NM]), Instr([PROV NM],",")+2,Len([PROV NM])-5-Instr([PROV
NM],",")), Null)
:
This MIGHT come close.
LastName:IIF(FullNameField Like "* DO" or FullNameField like "* MD",
Left([FULLNAMEFIELD],InStr([FULLNAMEFIELD]," ")-1),
FullNameField)
FirstNameAndInitial: IIF(FullNameField Like "*, DO"
or FullNameField Like "*, MD",
TRIM(Mid(FullNameField,
Instr(FullNameField,",")+2,Len(FullNameField)-5-Instr(FullNameField,",")), Null)
DoOrMd: IIF(FullNameField Like "*, DO" or FullNameField like "*, MD",
Right(FullNameField,2),Null)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
TotallyConfused wrote:
Thank you very much for responding. Here are sample of the names in my list:
Clooney, George A., MD
AnyName Pediatrics
Grace, Kelly, DO
ABC Medical Group, LLC
Medical Group at Vegas
McDreamy, Robert., MD
Metropolitan Hospital
Mayo Clinic
Any City Hospital
The list consits of either MDs or DOs and Medical Groups and Hospitals. The
MDs or DOs have a Middle Name or not. Everything else should stay as a full
name. Thank you again.
:
It would be helpful if you post a few examples. For instance, I might use
this if the name was like
Spencer, John P Jr, DO
LastNm:IIF(FullNameField Like "* DO" or
FullNameField like "* MD", Left([FULLNAMEFIELD],InStr([FULLNAMEFIELD]," ")-1),
Null)
But if the name was like
Spencer MD, John P Jr
then I would need to use a different algorithm to break this apart.
Especially if you might also have
Spencer MD, John Jr
Spencer MD, John P
Spencer MD, John
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
TotallyConfused wrote:
Hi I am having trouble parsing a full name in a field. However, in this list
there are degrees like MD, and DO. There are also company or group names.
What I am trying to do is to parse out all the last name, first name and
degree if there is one. If there is not MD or DO then leave full name in the
firstName field. This is what I have for my LastNM field. How do I write
syntax for First Name field and Middle Initial field. How do I write it so
that MDs or DOs are not separated? Thank you in advance for any help you can
provide.
LASTNM: Left([FULLNAMEFIELD],InStr([FULLNAMEFIELD]," ")-1)
