Results 1 to 5 of 5

Select last name from three or more names

  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    244

    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 is offline MS Community Contributor
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    1,907
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    43,291
    Won't handle names like Carl and Ingrid von Clausewitz.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,083
    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
    244
    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, 05:23 PM
  2. Replies: 9
    Last Post: 01-31-2017, 04:13 PM
  3. Replies: 11
    Last Post: 10-30-2016, 04:39 AM
  4. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 01:38 PM
  5. Replies: 5
    Last Post: 04-24-2011, 02: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
  •  
Tech Forums: Microsoft Office Forums