Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    5

    Question Unanswered: Separate out words from a single field

    I have a single field that contains [City State zip] . I would like to separate each of those into their own fields [City], [State], and [Zip].

    I tried using the "left" and "right" functions example:

    To extract the Zip I used right([City State zip],5) and for the most part worked, except sometimes the zip contained the extra code 70448-0450.

    To extract the State I used Left(Right([City State zip],8),3) and that worked again for the most part unless... you got it the Zip Code had the extra characters.

    Any suggestions?

    Thanks a bunch,
    Mike

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    take a look at the instr function

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Can you pull the records that have the extra zip,into their own table, do the parsing. Do parsing on the rest, then put it together?
    Ryan
    My Blog

  4. #4
    Join Date
    Aug 2007
    Posts
    5
    I tried the below expression and it gave me several different outputs.

    Zip Code =Trim(Right([Modified Roster]![Address2],InStr([Modified Roster]![Address2]," ")+1))

    Address2 Zip Code
    New Orleans La 70114 70114
    Elmira Ny 14901 Ny 14901
    New Orleans La 70114 70114
    Vermileon Oh 44089 On Oh 44089
    Dallas Tx 75208 Tx 75208

    Any ideas?

    Thanks again,
    Mike
    Last edited by Riddlemd; 08-16-07 at 15:44.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    How are the components of the addresses separated in the current field, i.e separated by spaces, by commas, sometimes one sometimes the other?

    Are states presented by full name or standard 2 letter abbreviation?

    If it would help, you can tell if an address has an extended zipcode like this:
    Code:
    If mid([City State zip],(len([City State zip])-4),1) = "-" Then
      Zip = right([City State zip],10)
    Else
     Zip = right([City State zip],4)
    End If
    Linq
    Last edited by Missinglinq; 08-16-07 at 16:16.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Got sidetracked mid-post! Are those addresses in Post # 4 representative of how they're all formatted; spaces between city, state and zip? With extended zips being xxxxx-xxxx?
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    try

    Zip Code =Trim(Right([Modified Roster]![Address2],Len(Modified Roster]![Address2]-(InStr([Modified Roster]![Address2]," ")+1)))

  8. #8
    Join Date
    Aug 2007
    Posts
    5
    This is an example of some of the data in th field:
    Sometimes there are the additional four 0s.

    [WINTER SPRINGS FL 327080000]

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Which they've entered in lieu of the extended zip part, I guess! Well, this just becomes more fun with every post! OK, assuming the zip code, in whatever form, is separated from the rest of the info by a space, this will always work:

    Code:
    Zip = Right([City State zip], (Len([City State zip]) - InStrRev([City State zip], " ")))
    I'll have to think about parsing out the city and state.

    Linq
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is the state always 2 characters?

  11. #11
    Join Date
    Aug 2007
    Posts
    5
    The Missinglinq code works great for the zipcode. I then modified it to trim out the extra 0s.

    The states are always 2 characters

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    OK, so you've got the Zip whipped now! These will take care of the City and State part:
    Code:
    City = Left([City State Zip],(InStrRev([City State Zip], " "))-3)
    State = Mid([City State Zip],InStrRev([City State Zip], " ")-2,2)
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Aug 2007
    Posts
    5
    Sorry for my late response... Everything is working perfectly now. Thank you so much for all your help.

    Mike

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Life will get in the way of writing code! LOL! Glad you got it working!

    Linq
    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
  •