Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Reverse names in one field

Discussion in 'Information Technology' started by AL1999 via AccessMonster.com, Nov 3, 2005.

  1. AL1999 via AccessMonster.com

    Guest

    Hi,

    I was wondering if there is a way to reverse a name in a single field. Say
    Ben Thomas to "Thomas, Ben" (without the quotes). I have tried one way:

    - Right([contactname]),InStr(Len([contactname]))

    But no luck. If anyone knows how to reverse a name, I would appreciate it.
    Thank you.

    Regards,

    Al


    --
    Message posted via http://www.accessmonster.com
     
  2. Loading...

    Similar Threads Forum Date
    Type 2 Diabetes in Newly Diagnosed 'Can Be Reversed' Health & Nutrition Jul 3, 2011
    USA Man Accused of Brutally Beating Sikh Reverses Plea Breaking News Apr 13, 2011
    SALDEF Policy of Discrimination Reversed: Sikh American Child Allowed to Enroll In Texas Public School Sikh Organisations Oct 29, 2010
    Sikh Coalition US Government Reverses Ban on Sikh Federal Security Officers Wearing Bana Sikh Organisations Oct 24, 2009
    Politics These are the names of 21 children killed in Gaza Breaking News Jul 12, 2014

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You can parse on the space and then concatenate the parts.

    Ah but there is a problem in using one field for names. Example as with a
    double first name - Billy Joel Thomas -- Mary Ann Smith.

    Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
    Left([contactname], - InStr([contactname]," "))




    "AL1999 via AccessMonster.com" wrote:

    > Hi,
    >
    > I was wondering if there is a way to reverse a name in a single field. Say
    > Ben Thomas to "Thomas, Ben" (without the quotes). I have tried one way:
    >
    > - Right([contactname]),InStr(Len([contactname]))
    >
    > But no luck. If anyone knows how to reverse a name, I would appreciate it.
    > Thank you.
    >
    > Regards,
    >
    > Al
    >
    >
    > --
    > Message posted via http://www.accessmonster.com
    >
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    AL1999 via AccessMonster.com wrote:
    > Hi,
    >
    > I was wondering if there is a way to reverse a name in a single
    > field. Say Ben Thomas to "Thomas, Ben" (without the quotes). I have
    > tried one way:
    >
    > - Right([contactname]),InStr(Len([contactname]))
    >
    > But no luck. If anyone knows how to reverse a name, I would
    > appreciate it. Thank you.
    >
    > Regards,
    >
    > Al


    Will you always 100% have two names with a single space?

    Of course the correct way is to divide the filed into first name and
    last name fields.

    --
    Joseph Meehan

    Dia duit
     
  5. AL1999 via AccessMonster.com

    Guest

    Hey Karl,

    I tried your method, it gives me an error. I have also tried to tinker around
    with it. Again, no luck.

    Regards,

    Al

    KARL DEWEY wrote:
    >You can parse on the space and then concatenate the parts.
    >
    >Ah but there is a problem in using one field for names. Example as with a
    >double first name - Billy Joel Thomas -- Mary Ann Smith.
    >
    >Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
    >Left([contactname], - InStr([contactname]," "))
    >
    >> Hi,
    >>

    >[quoted text clipped - 9 lines]
    >>
    >> Al



    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200511/1
     
  6. fredg

    fredg
    Expand Collapse
    Guest

    On Thu, 03 Nov 2005 00:28:29 GMT, AL1999 via AccessMonster.com wrote:

    > Hi,
    >
    > I was wondering if there is a way to reverse a name in a single field. Say
    > Ben Thomas to "Thomas, Ben" (without the quotes). I have tried one way:
    >
    > - Right([contactname]),InStr(Len([contactname]))
    >
    > But no luck. If anyone knows how to reverse a name, I would appreciate it.
    > Thank you.
    >
    > Regards,
    >
    > Al


    Well. I won't help you do what is bad Access programming (combining 2
    names within 1 field), but I will help you separate the names into 2
    separate fields (if your version of Access supports the InStrRev
    function).

    Add 2 fields to the table.
    Run an Update query:

    Update YourTable Set YourTable.LastName =
    Mid([CombinedNames],InStrRev([CombinedNames]," ")+1),
    YourTable.FirstNames =
    Mid([CombinedNames],1,InStrRev([CombinedNames]," ")-1);

    Once they are separated into 2 separate fields, it is a simple matter
    to combine them as you wish in a query or in a report.
    In a query:
    FullName:[LastName] & ", " & [FirstNames]

    or in a report:
    =[LastName] & ", " & [FirstNames]
    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  7. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    What kind of error?

    Did you try half of it - just the last name? Just the first name?

    "AL1999 via AccessMonster.com" wrote:

    > Hey Karl,
    >
    > I tried your method, it gives me an error. I have also tried to tinker around
    > with it. Again, no luck.
    >
    > Regards,
    >
    > Al
    >
    > KARL DEWEY wrote:
    > >You can parse on the space and then concatenate the parts.
    > >
    > >Ah but there is a problem in using one field for names. Example as with a
    > >double first name - Billy Joel Thomas -- Mary Ann Smith.
    > >
    > >Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
    > >Left([contactname], - InStr([contactname]," "))
    > >
    > >> Hi,
    > >>

    > >[quoted text clipped - 9 lines]
    > >>
    > >> Al

    >
    >
    > --
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200511/1
    >
     
  8. AL1999 via AccessMonster.com

    Guest

    Hey Karl,

    The error message says something, "the expression is typed incorrectly, or is
    to complex to be evaluated...". I copied and pasted the formula you wrote.
    But it keeps giving the above statement.




    KARL DEWEY wrote:
    >What kind of error?
    >
    >Did you try half of it - just the last name? Just the first name?
    >
    >> Hey Karl,
    >>

    >[quoted text clipped - 18 lines]
    >> >>
    >> >> Al



    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200511/1
     
  9. AL1999 via AccessMonster.com

    Guest

    Hey Karl,

    Yea it did work, there was a negative sign in the second Instr argument,
    that's why it gave me an error.

    Anyways, I appreciate your help and thank you.

    Regards,

    AL

    KARL DEWEY wrote:
    >You can parse on the space and then concatenate the parts.
    >
    >Ah but there is a problem in using one field for names. Example as with a
    >double first name - Billy Joel Thomas -- Mary Ann Smith.
    >
    >Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
    >Left([contactname], - InStr([contactname]," "))
    >
    >> Hi,
    >>

    >[quoted text clipped - 9 lines]
    >>
    >> Al



    --
    Message posted via http://www.accessmonster.com
     

Share This Page