How can I build an expression to extract text in two different formats?

[cliff.clayman]

I have a Name field in an Access table with different formats. Sometimes the name is formatted like Smith, John and sometimes it is John Smith. I am trying to pull the last name into a separate field. I created this to pull for the format with the comma, but how can I add an additional way to pull the last name when there is no comma?

LName: Left([FullName],InStr(1,[FullName],",")-1)

[June7]

Name parts should be in separate fields.

LName: IIf(InStr([FullName], ",")>0, Left([FullName],InStr(1,[FullName],",")-1), Mid([FullName],InStr(1,[FullName]," ")+1))

However, this assumes names are not like: Mary Jane Carter.

[cliff.clayman]

I do have some values that are like Mary Jane Carter. How can I extract that?

[orange]

Look at your data in very broad terms. Do you see any patterns?
I think you are sayiing

-LastName, FirstName
-FirstName Lastname
-FirstName MiddleName LastName

My experience says you may also see something like

Angela D'Orsini.....+Names containing single quote character
Paddy O'Malley.....+ Names containing single quote character
Ali van't Goor........+multipart lastname strange capitalization with embedded single quote
Oscar de la Hoya....+multipart lastname strange capitalization

Look for patterns. Solve each pattern.

Read a record
determine the pattern
call the Pattern Solver.

Good luck.

[cliff.clayman]

Ok, I adjusted me search name field, but now my match expression is not working.

Here is the code for the field that pulls out either the last name or the full name:

SearchName: IIf(InStr([Name],",")>0,Left([Name],InStr(1,[Name],",")-1),[Name])

Here is the code to see if the SearchName exists in the Name field:

Expr1: InStr(IIf(IsNull([Insured Name]),"N/A",[Insured Name]),Left([Name],InStr(1,[Name],",")-1))=0

It fails when the SeachName is the same value as the Name. How can I adjust the Expr1 code to work?

[June7]

How can that work at all? If [Name] has a varied structure as indicated by the SearchName expression, would have to do the same parsing of [Name] in Expr1. And if SearchName has only a last name extracted, how can the [Insured Name] be matched?

This is why name parts should be in separate fields. It is easier to concatenate strings than split them.

[orange]

cliff,

Please post a sample of your data (10-20 records).

[cliff.clayman]

Adams, Robert
ACS
Anderson, Jr,Chris
Jenkins, La'Roy
Ashner-Trujillo, Kelly
Arredondo Arevalo, Ezekial
Austin-Green, Esq., Alex
Az State Board
Bankers' Multiple Line Ins Co
Boop, Elizabeth "Betty"
Estate Of Arlene Patel
Johnson, III, Donald


These are about all the varieties that I could find. My current code seems to work for everything except for the names that do not have a comma in them. How can I add an exception to the code to just return the entire name field if there is no comma?


ChkName: InStr(IIf(IsNull([Client Name]),"N/A",[Client Name]),Left([Client_Name],InStr(1,[Client_Name],",")-1))=0


In my condition, I look for -1

[June7]

Test for the presence of comma first. See post 2.

[orange]

Cliff,

I am attaching a database that uses your sample data.
I have it set with vba routines to output :
---FName LName (suffix if applicable)
or--if it isn't a person, I simply output the original

You can go to the module, and run cliffnames.
The output is:
Robert Adams
ACS
Chris Anderson Jr
La'Roy Jenkins
Kelly Ashner-Trujillo
Ezekial Arredondo Arevalo
Alex Austin-Green Esq.
Az State Board
Bankers' Multiple Line Ins Co
Elizabeth "Betty" Boop
Estate Of Arlene Patel
Donald Johnson III


Good luck.

[cliff.clayman]

Nothing happens when I run the module...

[orange]

Cliff,
Did you look in the immediate window?
I just downloaded the database and ran the module, it put the extracted output in the immediate window.

[cliff.clayman]

No, I did not look there. I see it now. How can I apply this to my query?

[cliff.clayman]

Okay, I think I determined how to apply this to a query, but I am having a couple of issues. How do I handle NULL values in a name field?

[June7]

One way from query:

SplitName(Nz([fieldname],""))

or

SplitName(IIf([fieldname] Is Null, "", [fieldname]))

Review http://allenbrowne.com/QueryPerfIssue.html


The does assume that organizations do not use comma in name - that does appear to be the norm but is it possible to find comma in organization name?