Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Function that seperates text - Like pulling last name out of "John Smith"

    Hi. I hope you all are doing well today. I was wondering what the function was that allowed you to pull whole words out of a text field. I have a field of Sales People names, but I want to be able to sort by last name. So I figured I needed to find some way to pull out the last names. So if I had John Smith, Dana Cook, and Randy Harris, I would get Smith, Cook, and Harris. Any ideas?

    Thanks in advance for your help,

    David

  2. #2
    Join Date
    Dec 2001
    Posts
    79

    Re: Function that seperates text - Like pulling last name out of "John Smith"

    Originally posted by decad1
    Hi. I hope you all are doing well today. I was wondering what the function was that allowed you to pull whole words out of a text field. I have a field of Sales People names, but I want to be able to sort by last name. So I figured I needed to find some way to pull out the last names. So if I had John Smith, Dana Cook, and Randy Harris, I would get Smith, Cook, and Harris. Any ideas?

    Thanks in advance for your help,

    David
    We have a String Functions example database that includes a "get last word" function. You can download the example database here: http://www.peterssoftware.com/strfn.htm

    But you may want to consider if your people may have two word last names like "De Baets". In that case you don't want to end up with "Baets", so here's some more information that should be helpful:

    http://www.mvps.org/access/strings/str0001.htm

    Hope this helps,
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers
    http://www.peterssoftware.com

  3. #3
    Join Date
    Jul 2003
    Posts
    48

    Re: Function that seperates text - Like pulling last name out of "John Smith"

    You can also use a query to get this information, again you will have problems if you have someone with a two word last name. This query field uses a field called PersonName in your table and returns a field called Name.

    Name: Mid([PersonName],InStr([PersonName]," ")+1,Len([PersonName])-InStr([PersonName]," "))

  4. #4
    Join Date
    Jul 2003
    Posts
    48

    Re: Function that seperates text - Like pulling last name out of "John Smith"

    You can also use a query to get this information, again you will have problems if you have someone with a two word last name. This query field uses a field called PersonName in your table and returns a field called Name.

    Name: Mid([PersonName],InStr([PersonName]," ")+1,Len([PersonName])-InStr([PersonName]," "))

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Re: Function that seperates text - Like pulling last name out of "John Smith"

    Thanks. That did the trick!

Posting Permissions

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