Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Alphabetic Sort Order (Access 2000)

    Please help me to figure out how to sort data from query in Alphabetic order instead of Alphanumeric. I have a table with chemistry names such as 2-Chlorophenol, 4-Nitrophenol. I need to sort them based on first alpha character not on number.
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alphabetic Sort Order (Access 2000)

    Can you provide more info? Will all names consist of a number followed by a hyphen, then alphabetic characters? Or can there be multiple hyphens? Are there other patterns?

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alphabetic Sort Order (Access 2000)

    Please see attached file with all variations. Thanks a lot for your help.
    Yelena
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alphabetic Sort Order (Access 2000)

    Sorry for the naive questions - I'm not a chemist.

    Where does a name such as "bis(2-Chloroethoxy)methane" fit in the sort order?
    - Should it be sorted with the "b"s because it starts with a letter?
    - Or should it be sorted with the "C"s because Chloro... follows 2- ?
    - Or should it be sorted with the "m"s?

    And what about "Di-n-butylphthalate"?

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alphabetic Sort Order (Access 2000)

    All compounds should be sorted based on first alpha charachter. In your example - "b".
    Thanks for looking on this.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alphabetic Sort Order (Access 2000)

    You can create a query with a calculated column used for sorting; you can clear the Show check box for this column. With a field named ChemName, the calculated column would use the expression:

    IIf(Asc([ChemName])>64,[ChemName],Trim(Mid([ChemName],InStr([ChemName],"-")+1)))

    Explanation: if the ascii code for the first character of the field is greater than 64, we assume it is a letter (this is not true in general, but it is for your examples). We then return the full name.
    Otherwise, we use the InStr function to find the position of the hyphen in the name, and use the part of the name after the hyphen.

    See the attached sample database.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alphabetic Sort Order (Access 2000)

    Great! It WORKS!
    Thanks a lot!

Posting Permissions

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