Parsing out and acting on certain substrings in a string

[lewisteresa804]

Hi. I have an access 2010 query to break out certain sets of characters from a name field. We import this data which comes in as one field for name. We have to break it out into separate fields, last name and first name. But also to apply special treatment for certain occurrences. Obviously it is not consistent and that is the challenge.

The special sets are: etals, est, trustee, C/O, jr, III and several others. Of course, the order is not always consistent... sometimes est is the second word, or third or forth. I have tried using the switch function with limited success. Limited because again... the substrings are not always in the same order.

Here is some of my code just for treatment of the last name:

Lname: Switch([Account Name] Like '* EST *',parseword([Account Name],1) & " " & 'EST',[Account Name] Like '*TRUST*',parseword([Account Name],1) & " " & 'TRUSTEE',[Account Name] Like '*C/O*',parseword([Account Name],1) & " " & 'C/O',[Account Name] Like '*%*',parseword([Account Name],1) & " " & '%',[account name] Is Not Null,parseword([account name],1))

As you can see - I am using parseword and extracting the first word....which I know is wrong because I don't know by using the LIKE operation exactly which word is "est",etc. Further, I am trying to avoid the iif function because of all the nesting. For the most part, I need to have a result where the first word is treated as Last name (which is about the only thing that is consistent) and if there any of the substrings are present to append that to the last name preceeded by a blank... then another challenge is figuring out what to put in the first name.

Here is a sample of some of the data...which I display as broken into separate columns for better understanding but it comes in as one field. I appreciate any assistance

KING	HELEN	S	ETALS
KISER	MARY	V	E	ETALS
LINKOUS	FRANK	J	&	EDNA	M
MONROE	FRED	&	EMMA	EST
DUNFORD	PERRY	G	SR	&	BRENDA	C

[orange]

You have a business problem not a database problem at present.
If someone is supplying the data to your organization, perhaps some negotiation on data format is in order.

If you are interested in standardizing a mess, then you will need to understand the string manipulation functions in access.
Right, Left, Mid, InStr etc. you can find out more about these at

http://www.techonthenet.com/access/functions/

You should really have a list of SpecialTerms and add to the list as needed.
To do this with as much automation as you can, you need to be looking for the SpecialTerms.

[June7]

Consistency in structure is essential to program parsing of text.

The string is not delimited with a character such as a comma? A space is not a reliable delimiter because of names like von Danikan or van der Beek.

Then distinguising between name and non-name parts is another challenge.

I have never seen 100% foolproof code for parsing name data.

I presume parseword is a custom function.