Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting Title from Names (Access 2000)

    I have several databases that I would like to combine. I need to extract the title, first name and last name from a field that has only the complete display names. eg. Mr. LastName, Mr. & Mrs. FirstName Last Name, Dr FirstName LastName and just FirstName LastName) I need to convert to [Title] [FirstName] [LastName]. No problem doing it with just FirstName and LastName but the titles in some of the records throw me. Any ideas?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Title from Names (Access 2000)

    To extract the titles you could use the following expression :

    Left([fullname],(InStrRev(Left([fullname],InStrRev([fullname]," ")-1)," ")))

    Replace fullname with the name of your field.

    This expression suppose that there is no space in the firstname or lastname.
    Francois

  3. #3
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Title from Names (Access 2000)

    Yes, I understand how this works but my problem is that the single record sometimes is for both "Mr. and Mrs." FirstName LastName.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Title from Names (Access 2000)

    The expression will give you Mr. & Mrs. as in the expression I first remove the last name and then the first name with the InStrRev function
    Francois

  5. #5
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Title from Names (Access 2000)

    Yes, I now understand. Thank you, that will work great.

Posting Permissions

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