Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help Parsing a Field? a2k (9.0.6926) SP-3 Jet 4.0

    I have a field called FirstName and it can contain first name, middle name or middle initial, examples of combinations follow:

    I want to split it into 2 fields First Name and Middle Name with query or function

    <pre>
    FirstName First Middle
    A A
    JOHN JOHN
    ROBERT EDWARD LEE ROBERT EDWARD LEE
    AUGUST E A AUGUST E A
    D HARMON D HARMON
    FRANK CHAUNCEY FRANK CHAUNCEY
    ALBERT M ALBERT M
    </pre>


    Thanks, John

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Parsing a Field? a2k (9.0.6926) SP-3 Jet

    Within a query - the first displays everything before the first space. The second displays everything after the first space.

    First: Left([firstname],InStr([firstname]," ")-1)
    Middle: Mid([firstname],InStr([firstname]," ")+1)

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

    Re: Help Parsing a Field? a2k (9.0.6926) SP-3 Jet 4.0

    The 2 strings that mbarron gave you will ONLY work if there is a space in the old first name field. Therefore, make sure you query has a selection specification that checks for existence of a space in the first name field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Parsing a Field? a2k (9.0.6926) SP-3 Jet 4.0

    Hi mbarron & Mark

    This seems to have gotten it

    First: IIf(InStr([mfirst]," ")=0,[mfirst],Left([mfirst],InStr([mfirst]," ")-1))
    Middle: IIf(InStr([mfirst]," ")=0,Null,Mid([mfirst],InStr([mfirst]," ")+1))

    Thanks for your help, John

Posting Permissions

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