Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating name (2002)

    I have a field with a name John H. Doe
    I want to make a field with First name (FN) only and one with Last Name (LN)only No middle initial
    What is the query code to do it?

    Thanks

    Paul

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

    Re: Separating name (2002)

    If all names are of this form, you can use:
    <code>
    FN: Left([FieldName],InStr([FieldName]," ")-1)

    LN: Mid([FieldName],InstrRev([FieldName]," ")+1)
    </code>
    where FieldName is the name fo the field.

    Note: InStrRev will only work in expressions in Access 2002 or later.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating name (2002)

    Thanks Hans - worked Great!

    Paul

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Separating name (2002)

    Just a Note, InStrRev also works in Access2000.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Separating name (2002)

    I know that InstrRev together with several other functions such as Replace and Round was introduced in Office 2000 VBA, but according to ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions they can't be used directly in queries and in the control source of text boxes. If InstrRev does work there, all the better.

Posting Permissions

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