Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Separate City, State and Zip

    Hi:
    Can any one please tell me how do i separate city state and zip. I ask this question in previous post. But when the city name is in two words it mess up my values otherwise it works.

    CityStateZip
    -------------
    Las Vegas NA, 12345
    Oradell NJ, 07649
    Bay Shore NY, 11703

    Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Replace strAddress in the code below with the actual name of the string that contains your entire address, and this code will work regardless of the number of words in the city name, assuming your addresses are all formatted the way your examples were.

    Code:
    Dim lngAddressSize as Long
    lngAddressSize = len(strAddress)
    
    City  = Left(strAddress, lngAddressSize-10)
    State = mid$(strAddress,lngAddressSize-8,2)
    Zip = Right(strAddress, 5)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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