Results 1 to 3 of 3

Thread: Name Reversal

  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If this problem is so simple, why couldn't I remember how to do it or find it in the Forum.

    FieldName: Winners

    It contains a person's name in this format:

    Smith, John
    Doe, Mary

    All I want to do is reverse them in my query. I think that it'll take three fields to do it.

    Field 1 for Surname up to the comma
    Field 2 for First Name from after the comma
    Field 3 to combine Field 1 & Field 2
    Cheers,
    Andy

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You could do as you suggest and have:

    surname: Left([winners],InStr([winners],",")-1)
    firstname: Right([Winners],Len([winners])-1-InStr([winners],","))

    Then join them as Winner: [firstname] & " " & [surname]

    or do it in one winnner: Right([Winners],Len([winners])-1-InStr([winners],",")) & " " & Left([winners],InStr([winners],",")-1)

    This assumes there is always a comma after the surname, then a space before the firstname in the original data.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    Thank you kindly for your help. I thought I was heading in the right direction, but couldn't find the references that I needed. Your one-pass solution is elegant!
    Cheers,
    Andy

Posting Permissions

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