Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    New York City, North Carolina, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting/Alphabetize (2000)

    Is there a way to sort from within a cell from the last word? Meaning I have a cell that has "NY, NY 10010" and i would like to sort it by the zip code? In Word Perfect you can sort by adding "-1" to the sort but I don't know how to do that in Access..

    Second, how can I sort two columns at one time....like, last name then first name

    Thanks much

    kit

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Sorting/Alphabetize (2000)

    If there's some reason you definitely don't want the zip code to be a separate field, you could base the relevant report (or subform, or control, or whatever) on a query and include a calculated field in the query that consists only of the zip code, and sort on that calculated field.

    If the zip code will always be preceded by the last space in the full field, one way to pull the zip code (alone) out of it would be to use the built-in Mid and InStrRev functions, as follows:

    strZipCode = Mid(strFullField, InStrRev(strFullField, " ") + 1)

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    New York City, North Carolina, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting/Alphabetize (2000)

    Thanks much for your response....I am not that familiar w/ACCESS so I will probably attempt the first response. I appreciate your response.

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    New York City, North Carolina, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting/Alphabetize (2000)

    It just may be that I missed the previous answer; however, I appreciate your post. I originally brought the table in from Word and therein lies the problem...I should have set it up in separate fields from the start. I didn't think to bring it back over and start again.....Thanks for your response. Just wish there was an easy way to do it in ACCESS; but alas, nothing ventured, nothing. Thank you again.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sorting/Alphabetize (2000)

    Hi Kit,

    Is this related to a previous post of yours relating to sorting by zip code? The real answer is to put the City, the State, and the ZIP into separate fields, as Charlotte suggested previously. There are several ways to do that automatically in a table coming in from Word, or you can export the table in Access into a text file, and then use the tools within either Word or Excel, whichever you are most comfortable with, to create a multi-column table or worksheet, and then reimport the table into Access with the appropriate fields. That will also let you use the bar-code feature in Word if you so choose.
    Wendell

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sorting/Alphabetize (2000)

    I was referring to your <post#=280986>post 280986</post#> and Charlotte's response in <post#=280996>post 280996</post#> from August of 2003. To do the splitting into separate columns in Access requires the use of functions and update queries, but doesn't require any VBA. You basically use the Left(), Mid() and Right() string functions in combination with the Instr() function when necessary, and update new columns in your table based on those expressions. Word and Excel both have some built-in capabilities to convert text to columns using delimiter characters, which would probably work just fine in your case. On the other hand, you might want to learn a bit more about Access and the real power of databases in sorting, filtering and so on.

    I also failed to respond to your question about sorting on multiple columns. If you are doing it in the table view, you simply highlight the two columns and then specify which sort direction you want on the toolbar. Note that the left-most column will be sorted first, then then next and the next. You can reorder columns as needed by simply clicking on the column (field) name, and dragging it to the desired position. If you want to do more complex sorting, you should create a select query, where you can specify different orders for different columns, and you can also specify criteria to limit the returned data. Hope this helps.
    Wendell

Posting Permissions

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