Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Str Sort Order in Query (Access 97 & 2000)

    In WinXP Pro... If I have a sort code string of "0001" in one record and a sort code string of ":001" in another record and sort by that field in a query, I expected the "0001" record to fall before the ":001" record because the colon has a higher value than a zero character in the ASCII table. However, in Access 97 I'm getting the opposite result! I haven't tried it in Access 2000 but I have to move the stuff I'm developing into Access 2000.

    Why is this happening and what leading character can I use to make one set of data fall between the number group and the letter group?

    Thanks,
    Bill

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

    Re: Str Sort Order in Query (Access 97 & 2000)

    This does sound strange - the hex code for "0" is x0030 and that for ":" is x003A so it should sort the way you want it. A couple of possibilities - are you sorting with a query and is it doing some kind of formatting, or is it on a form where you are specifying a font that has the colon as some other character?
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Str Sort Order in Query (Access 97 & 2000)

    Bill is correct. It seems to be the same on Access 2000 and 97.
    All the characters from chr(58) to chr(64) are concerned.
    These are the ascii characters between 9 and A.
    Changing the option compare to binary or to text don't change anything.
    Francois

  4. #4
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Str Sort Order in Query (Access 97 & 2000)

    I did some investigating and found that all the printable non-alphanumeric characters on the keyboard fall before the numbers when sorted by a query! The query sort is also not case sensitive so it is obviously using a sort algorithm that manipulates the underlying codes. I solved my problem by adding a prefix character to every sort key instead of just some of them like I was trying to do. So, the sort is working fine now.

    Thanks for responding.
    Bill

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Str Sort Order in Query (Access 97 & 2000)

    The problem appears to extend beyond query sorts. It sorts the same wrong way when placing an index on the field in the table definition and viewing the table in datasheet view.
    Carol W.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Str Sort Order in Query (Access 97 & 2000)

    Might the difference be in the unicode character set and data storage in A2K and up, compared to ascii in A97 and below?

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Str Sort Order in Query (Access 97 & 2000)

    Dean:

    I tried changing unicode compression from yes (the default) to no on the index field. It didn't seem to make a difference in the sort sequence, either in the table datasheet view or in the query datasheet view.
    Carol W.

Posting Permissions

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