Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separate City, St, Zip (2003, SP2)

    it has been forever since I have been here and it always seems to be a simple question that I cannot answer that brings me back... what would we do without Woody's.

    So... I got a customer file with 255,000+ records in a table in Access. The City, State and Zip are combined in one field. Grrrr. The good news is that there is a double space between each component.

    for example one record has [PHILADELPHIA PA 19144 3836] in one field and I need City: [PHILADELPHIA] State: [PA] Zip: [19144 3836]

    How can I do it?

    BTW: I have a new email address and I am afraid that my account here has an old one - where is my profile info so I can edit the email?
    I will check back often for this answer and hope to change my email soon.

    As always - THANKS! My questions are not real challenging but important to me.
    There is always a way.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separate City, St, Zip (2003, SP2)

    I GOT MY EMAIL corrected... now for the real issue.
    There is always a way.

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

    Re: Separate City, St, Zip (2003, SP2)

    For the City
    city: Left([csz],InStr([csz]," ")-1)

    For the State:
    state: Mid([csz],InStr([csz]," ")+2,InStrRev([csz]," ")-InStr([csz]," ")-2)

    For the ZIP:
    ZIP: Right([csz],Len([csz])-InStrRev([csz]," ")-1)

    Replace the [csz] with the correct name of your field.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separate City, St, Zip (2003, SP2)

    PERFECTION!
    Thank you!
    There is always a way.

Posting Permissions

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