Access module/procedure to break out names

[Guest_imported]

Can any one provide me with a procedure to break out first name, middle initial, last name or other variants on the name formats? I need to do a comparison between names in different tables.

 

[MichaelRed]

This is a lot harder than it seems like it should be. Users and dataentry are the bane of every database (I think htey should be BANNED!). It is easy to seperate the parts, it is the interpertation of the parts which get you in trouble. In particular, the "address" (Mr., Mrs., Ms., Dr., Adm., ... ad infinitum) and the "title" (III, Jr., USN Ret., ...) will cause you a great deal of anguish. If you are using Ms. Access 2K, htere is a function "Split" which will give you the seperate "words" in the name string. I you are using an earlier version, I have posted (somewhere in these Ms. Access Forums) a function "basSplit" which will cover the seperation of hte individual words. As far the titles and adresses go, you have my sympathy.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Durkin]

I had to split names like this before and I found a way that was quite easy and didn't involve trying to think of every possible way some could write their name. What I did was to use Outlook. If you make a reference to Outlook in your DB and then declare a 'Contact' object, this object has a property called 'Fullname' or something. If you have ever entered a contact in Outlook you will have seen that when you enter someone's name it splits it up into the various parts. Using your contact object assign the fullname that you have to the 'Fullname' property. You should now have 'FirstName', 'LastName', 'MiddleName' and 'Title' properties which you can refer to. I may be incorrect in the names of all properties as it's a long time since I wrote that code and I haven't got Outlook in my current position. It doesn't always get it right but if you have a quick check through the names after you have run the routine you should spot any obvious ones. I seem to remember that with names like O'Reilly etc., if they didn't have the apostrophe, would have the O as a middle name. If you need any further clarification or help, just write back. This problem comes up quite alot, if only we could assign everyone a serial no. or stick a barcode on their forehead the world would be a happier place. LOL Durkin
alandurkin@bigpond.com

 

[THoey]

I have done this before using Access and it's string functions (INSTR, SUBSTR, LEFT, RIGHT) to parse the full name, but as Michael said, this is made more difficult when you add in Mr. and Mrs. or Jr. and Sr. I know I have read his posts before where he swears by NOT having a full name field and I agree with him. The minimal overhead it takes to handle the extra number of fields is far over shadowed by the ease of use in manipulating the data.

Durkin, it would be easier if we had a number, and for a while I think at least the U.S. was heading that way with the Social Security Number. But I think the fear of "just being a number" has slowly caused us to pull away from that. So now, instead of being one number and being able to associate all data with it, we have account numbers and pins for each account, drivers license numbers, SSN's, employee numbers, etc... It's a shame that even the best of ideas get contorted and skewed until they are dropped like pregnant chad on the ballot room floor...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[PaulVD]

This question remembers me a vba module in a microsoft downloadfile entitled: "neatcode.exe". It is about 'string manipulation/parsing'.