InStr Function

[Craig]

I am using the InStr function in combination with Left to split up a name
field - as follows:

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

This works mostly except if there are two names such as Jo Anne, Ann Marie.

How can I rewrite this to extract the first names.

I see two parts to this:
1. to look for the second "space" in a name field
2. To determine if its a name or an initial by counting the number of
charachters in the second part of the name. Jo Anne Smith / Jo A Smith. Jo
Anne A. Smith

Any help would be greatly appreciated

Craig

 

[Guest]

Parsing names is almost impossilbe in any programming language. There are
too many variations to deal with. You can get close, but it will never be
100% accurate. To get even close, it will take more than you can do in the
query itself. You will probably need to write a function and call it from
the query.

For future reference, always carry names as separate fields. It is much
easier to concatenate them for display purposes.

 

[raskew via AccessMonster.com]

Craig

Lookup the InStrRev() function. Starting with A2000, it allows you to
extract the last position of a character/space in a string. If you happen to
be using A97, post back and I'll provide an A97 roll-your-own substitute.

Bob

I am using the InStr function in combination with Left to split up a name
field - as follows:

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

This works mostly except if there are two names such as Jo Anne, Ann Marie.

How can I rewrite this to extract the first names.

I see two parts to this:
1. to look for the second "space" in a name field
2. To determine if its a name or an initial by counting the number of
charachters in the second part of the name. Jo Anne Smith / Jo A Smith. Jo
Anne A. Smith

Any help would be greatly appreciated

Craig

 

[John Spencer]

Even using instrRev is problematic

John P. Spencer, Jr
Oscar de la Renta
Jo Ann Van Diver
Surang Mac Intyre
Madonna
Mao Tse Tung

In other words, there is no simple solution to the problem. I've seen VBA
routines that split the name on all spaces and then use pattern matching and
exceptions table to come close.

IF the last name is always the last word the InStrRev function is probably
your best bet.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Craig

Lookup the InStrRev() function. Starting with A2000, it allows you to
extract the last position of a character/space in a string. If you happen
to
be using A97, post back and I'll provide an A97 roll-your-own substitute.

Bob

I am using the InStr function in combination with Left to split up a name
field - as follows:

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

This works mostly except if there are two names such as Jo Anne, Ann
Marie.

How can I rewrite this to extract the first names.

I see two parts to this:
1. to look for the second "space" in a name field
2. To determine if its a name or an initial by counting the number of
charachters in the second part of the name. Jo Anne Smith / Jo A Smith.
Jo
Anne A. Smith

Any help would be greatly appreciated

Craig