Separating 1 "Customer" text field into 2 "name" text fields

[mplsdoors]

I am a new programmer at a new job creating an application. Our users are typing, into 1 field, customer names in all sorts of ways.
Ex: John Smith; Smith, John; Smith,John; Smith; SMITH; Mr. John Smith.

How do I take a field that contains a mishmash of information and pull a first name and last name? Since my firstname and lastname fields will be feeding into important form letters I would like to have only the first letter capitalized. If the customer field only contains one word, I am supposed to add 'Residence.' For example: "Smith Residence."

I've been experimenting with "InStr" and have figured out how to pull the last name when the name is like: 'Smith, John', but that is all I have been able to do.

I'm a beginner and this is beyond what I was taught in school. I've looked for clues on the Internet for the last 3 days.

Thanks,
Michelle

 

[MichaelRed]

Michelle,

You have stumbled into one of the larger quagmires in the data world. Sorry, but this is a monumental task. It has been discussed in several threads in the Ms. Access forums, and I will not repeat them here, except to say that there are NO easy or 'canned' soloutions to the existing hodge podge and you need to change the way the "information" is collected to keep the issue from getting worse. When the search capability is available, you can do the key word search on several terms to review the pevious postings. I would suggest that you start with "Names" as the first key word.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MaeJams]

Michael is right...before your dataset gets too large, you will probably need to change the data structure to have three separate fields (Title (Mr, Mrs, etc.), First Name, and Last Name) and modify the entry form accordingly. You can have a set of radio buttons that equate to the Title and regular text boxes for the First Name and Last Name.

Best of luck.
Rich