Results 1 to 5 of 5
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Select last name from three or more names


    Using A2007. I want to separate first and last names into separate first and last name fields. I can make a name like "Joe Smith" into two fields; Smith and Joe But how do I separate a name that is entered as "Joe and Mary Smith" or "Joe & Mary Smith" or " Joe Michael Smith" into Smith in the last name field and the other names in the first name field? Any help much appreciated.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Use the following expressions as calculated fields in a query:

    Code:
    FirstNames: Left([YourFieldName],InStrRev([YourFieldName]," ")-1)
    Code:
    LastName: Mid([YourFieldName],InStrRev([YourFieldName]," ")+1)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Won't handle names like Carl and Ingrid von Clausewitz.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you do some analysis on the data.
    Look for some patterns --eg: containing words like "and","&", "or"
    Look for compound names eg Jean Paul Jones, Oscar de la Renta, Ali vant Goor, Gidah el Mordanni

    You can remove some things in steps.
    Some you'll have to make guesses for.
    Parsing names has plagued developers for years.

    Good luck.

  5. #5
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Works!-Never seen InStrRev before. Thanks very much and will heed mdb recommend in future posts..


    Quote Originally Posted by Bob Fitz View Post
    Use the following expressions as calculated fields in a query:

    Code:
    FirstNames: Left([YourFieldName],InStrRev([YourFieldName]," ")-1)
    Code:
    LastName: Mid([YourFieldName],InStrRev([YourFieldName]," ")+1)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-08-2018, 06:23 PM
  2. Replies: 9
    Last Post: 01-31-2017, 05:13 PM
  3. Replies: 11
    Last Post: 10-30-2016, 05:39 AM
  4. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  5. Replies: 5
    Last Post: 04-24-2011, 03:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums