Results 1 to 3 of 3
  1. #1
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    Incorrect Binary-based ASCII sorting in tables

    This is a duplicate of thread: http://www.access-programmers.co.uk/...d.php?t=218622



    (looking for any advice on this subject, in terms of whether this is an Access bug or not).

    All,

    I just noticed that Access tables do not sort special (characters) in the correct order, in binary terms. For instance, I have a table with one field as such:

    Table1
    AC
    -C

    sorting in Ascending order, 'AC' comes first then '-C'. However, 'AC' is '4143' in hex, whereas '-C' is '2D43' in hex. Obviously, when you look at that in hex, it looks backwards. Hence, the table is not sorting the values properly.

    Is it sorting the values properly, but special characters are treated differently? Is it really sorting in binary? Or is the sorting just ASCII-based?

    Not too keen on this subject, can someone give me a boost here for understanding this issue?

    Reason - sorted data of mine goes into a system that reads binary files (made by data I produce in Access). However, if someone requests data from a specific location with a special character like "-" in it, the location is incorrect because of the sort order of the records that the binary-based mechanism is expecting. However, from an Access-based perspective, the sort order IS correct.

    However, the customer uses the binary-based machine.

    thanks guys!

    <EDIT>
    apparently, 1-byte characters (single characters) do not cause an issue. It happens with character strings that are more than 1 char long. for instance, this is the ASC sort order for one-char strings:

    Table1
    -
    A

    and that is correctly sorted, in hex. ??

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you played with the Option Compare value?

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what are you talking about here Allan? Option Compare in VBA? Not sure what you mean by this.

    For an example of what I'm talking about, I uploaded a small example file in the other thread, here:

    http://www.access-programmers.co.uk/...d.php?t=218622

Please reply to this thread with any new information or opinions.

Similar Threads

  1. OpenTextFile, unicode/ascii
    By dssrun in forum Programming
    Replies: 4
    Last Post: 11-23-2011, 01:22 PM
  2. Binary datatype
    By huBelial in forum Access
    Replies: 1
    Last Post: 04-04-2011, 02:02 PM
  3. Sorting/Subtotaling pivot tables
    By Tina in forum Access
    Replies: 0
    Last Post: 08-25-2010, 10:34 AM
  4. Incorrect sorting in report.
    By jonesy29847 in forum Reports
    Replies: 2
    Last Post: 06-16-2010, 05:56 PM
  5. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums