Results 1 to 11 of 11

Thread: parseing info

  1. #1
    jelks
    Guest

    parseing info

    Hellow fellow Accessers!
    I am trying to parse some information from a total name field with varying information in it. I have used the instr(), mid(), left(), right() and IIF() to get city, state, zip, country, but the name has me befuddled. The name has varying lengths like this:

    John P. Doe
    Mr. Johanne P. Doe
    Juan Doe
    Doe
    Juanito Doe, III
    Dr. John Citizen
    Ms. Juanita Ruiz Cruz

    What I would like is to filter the Salutation(if any), FirstName, MiddleInitial (if any), LastName.

    Apologize for the long-winded post.
    Email response if possible: jason@chitester.com

    Thanks in Advance all you ACCESSorizers

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    Jason: I used this to separate a name field into first and last. Format of [name] is [first last]. You might be able to modify it for your use. Hint: Always keep names separate in your DB. Much easier to work with title, first, mi, last. HTH.


    SELECT tbl_Names.Name, InStr([Name]," ") AS [Temp Name], Left([Name],[Temp Name]-1) AS [First Name], Mid([Name],[Temp Name]+1) AS [Last Name] INTO tbl_Split_Names
    FROM tbl_Names;

  3. #3
    jelks
    Guest

    Re: parseing info

    BC:
    Thanks for the help, however it doesnt solve my problem of the title in the beginning. I guess I will have to do a series of nested IIF's to first, continue parsing after the first or second space.
    I am still looking for more help If anybody has any more suggestions.

    Jason Elks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    I had to do this once many years ago to convert an email address file from an old system to MS Mail (yes, before Exchange and Outlook). It ended up being an incredibly complex VB program that required interaction for every one of the 60,000 name fields. Do you plan to handle names like Jean-Pierre van den Hagen? How about My A? I had one case where the last name was 5 words. I also ran into some very interesting titles and suffixes (Jr., II, 2nd, etc, not to mention mine which is CISSP)

    Good luck!
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    Jason:

    1st, commiseration. Normalizing names approaches the worst thing you'll have to do with data - we'll leave the worst thing unnamed! <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

    As far as the title goes, I have this suggestion. Make a lookup table that has all of the possible titles: "Mr.", "Mr", "Mssr", etc. In a query, pull all the data before the first space in the unnormalized name. Match that against the lookup, table; if it matches, you can write it to a Title field in a resulting table, if it doesn't, you don't. No matter how you go about this, there's bound to be some exceptions that may need manual attention. That's why they make Credit Managers and A/R staff! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> (I love that little guy!)

    I also saw <A target="_blank" HREF=http://www.users.bigpond.com/wemba/splitter.htm>this</A> add-in featured in the Dec. 2000 issue of Element K's Inside MS Acces. I've not tried it, but you might want to give it a look.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: parseing info

    You might also want to take a look at <A target="_blank" HREF=http://www.guysoftware.com/parserat.htm>ParseRat</A>, which handles names along with other kinds of data. In the meanwhile, you didn't mention the version of Access you're using. If it's 2000, you can use the Split function to parse the words of the name into a single dimension array and then test the elements to see what they are.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    Dear Jason,

    Parsing out data can be difficult especially if there is little or no structure to it. Your best option might be to go for one of the programmes identified by Shane & Charlotte. However if you do want to have a go yourself, this may give you a start.

    I once used a dos DB called
    Attached Files Attached Files

  8. #8
    jelks
    Guest

    Re: parseing info

    Thank you all for your help. You have been most helpful. I have the first name and title parsed. I now have to parse the rest. hurumph! I am going to give ruperts functions a try.

    jason

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    I got an unrecognized format after unzipping and trying to open it in Access 97. Is this Access 2000 by chance? <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    Thanks for that query...
    Instead of going the long, arduous way of arrays, binary coding/testing, which is no doubt the correct way, I decided to query it out

    Your script is working and by substituting the criteria, I am getting the desired results.

    Seeing that I have to do this over and over, how would you UPDATE a table to include these new fields rather than make a new table?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parseing info

    Dear Jason,

    Access 97 ver as requested.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •