Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Northfield, Minnesota, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Firstname from NAME field (Access 2000)

    I have been trying to write a query to get the first and last names into seperate fields from a name field where they are concantenated.
    Problem is that I don't know how far to tell it to go.
    Irwin Alec C
    Johnson Keith J

    These names have the space between them in a different location. How can I find the space and then tell Right or Left to take that many characters
    Left([Name],?)

    Thank you for all of your help.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Firstname from NAME field (Access 2000)

    Are they all groups of three?
    >>Irwin Alec C <<< 3 Words
    >>Johnson Keith J <<< 3 Words

    or could you have:
    John Smith >>>> 2 words
    Mr G J Jones >>>>4 Words

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

    Re: Firstname from NAME field (Access 2000)

    If the last name is always a single word, you can use<pre>LastName: Left([FullName], InStr([FullName], " ") - 1)</pre>

    and<pre>FirstName: Mid([FullName], InStr([FullName], " ") + 1)</pre>

    This will result in LastName = "Irwin", FirstName = "Alec C". If you want to split off the middle initial form the first name, the expressions become more complicated, but it can be done. Post back if you need that. If there are double last names containing a space, it becomes much more complicated.

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Northfield, Minnesota, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Firstname from NAME field (Access 2000)

    Thank you I think that this is what I was looking for.
    Yes, the names are sometimes with a middle initial and sometime not. But it will work just fine to put the middle initial in the firstname field.

    Thank you for all of your help
    Alec

Posting Permissions

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