Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query to separate (Access 2000)

    I have an import field with lastname, firstname
    Is there a query which would separate everything in front of the comma into one field, and everything after the space into another field?
    Thanks so much for your expertise.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: query to separate (Access 2000)

    You could use the following in your query to do this. It may need minor adjustments due to the space after the comma.

    I will assume that your Last,First in Field1 in a table.

    Last: Left([Field1],InStr([field1],",")-1)
    First: Right([Field1],Len([field1])-Len(Left([Field1],InStr([field1],","))))

    Hope this gets you started. I am sure someone wrote a function somewhere in Woody's to do this.
    Regards,

    Gary
    (It's been a while!)

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

    Re: query to separate (Access 2000)

    Gary has already replied; his expressions should work fine. A slightly shorter "formula" for the first name would be:

    <code>First: Mid([Field1],InStr([Field1],",")+2)</code>

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: query to separate (Access 2000)

    I like your answer better - much easier to understand. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query to separate (Access 2000)

    Thank you both- they work wonderfully!

Posting Permissions

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