Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unanswered: Parsing City, State Zip Column in Access

    Hey Everyone:

    I have a column for about 1700 records that contains data that looks like this:

    San Diego, CA 92115


    I want to split that data into 3 seperate columns: one for city, one for state, and one for zip. What kind of SQL or Access query would do that for me?

    Thanks for your help.

    -Nathan

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    is all your records ends with XX XXXXX format? I mean like Fl 33347. it could be more easy if that was the case.
    ghozy.

  3. #3
    Join Date
    Aug 2004
    Posts
    6

    Address Parsing Reponse

    Yes, they are all in that format

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    If it's the 2 char state code and 5 char Zip then it's easy. For safety's sake create a duplicate address field and update it with the same values as the original field (address, state zip) just in case you make a mistake. This 2nd field will end up as the street address field.

    In short:

    [Address1] field (as the backup)
    [Address2] field (same values as above)
    [State] field 2 chars
    [Zip] 10 characters (text not number and for ZIP+4 when needed)
    Update [Zip] with Right$([Address2],5)
    Update [Address2] with Trim(Left$([Address2], (Len([Address2])-5)))
    Update [State] with Right$([Address2],2)
    Update [Address2] with Trim(Left$([Address2], (Len([Address2])-2)))
    [Address2] will become the new Street address field

Posting Permissions

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