Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing (Access 2002)

    I saw the interesting question by mrabrams about getting rid of the middle initial and Hansv's answer. I have a slightly different problem:
    Inconsistent data entry like the following:
    Armstrong Jr., Peter L.
    Turner Jr., Ben
    Turner, John
    La Johns, Joan
    For all of these, I need to split out the last name, first name and middle initial.
    I can handle the middle initial, last name as long as there is not "," befor ethe jr. It is the firs tname that gives me trouble.

    Any suggestions?
    Thank you for any and all help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing (Access 2002)

    You state (more or less) "as long as there is not "," before the Jr." But can that occur? We need to know ALL patterns that can occur, otherwise someone will devise a formula, you point out an exception, etc.

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

    Re: Parsing (Access 2002)

    Assuming that the *last* comma in the value delimits the first name or first name plus initial, you can use Mid([NameField],InstrRev([NameField], ",")) to return the first name and/or first name plus initial. Then you could handle the result.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing (Access 2002)

    I am aware that I cannot handle all possibilities of inconsistent data entry. I am just trying to handle most of them. Then I allow the user to edit the bad results. The list I put into my original post summarizes about 90% of the data entry. For armstrong Jr., Peter L. for instance:
    I can get the last name to the ",". I get the middle initial. Its the first name in the first example that messes me up. I am showing my expression below:
    PatientFirst: IIf(Right([patient],1)=".",Trim(Mid([patient],InStr(1,[patient]," ")+1,InStr(InStr(1,[patient]," ")+1,[patient]," ")-InStr(1,[patient]," "))),Mid([patient],InStr([patient],",")+2))
    This gives me ",Jr." as a first name.
    I have a feeling that I am missing something here.

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing (Access 2002)

    How about this? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    PatientFirst: IIf(InStr(InStr([Patient],", ")+2,[Patient]," "),Mid([Patient],InStr([Patient],", ")+2,InStr(InStr([Patient],", ")+2,[Patient]," ")-InStr([Patient],", ")-2),Mid([Patient],InStr([Patient],", ")+2))

  6. #6
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing (Access 2002)

    That did it!

    Thank you - Vielen Dank!!!

Posting Permissions

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