compare/sort order (1 Viewer)

[AlanS]

I'm using Access 97 under Windows XP, with all Control Panel "locale" options set for the US. I'm reading records from a linked table (in another Access 97 database) using DAO, and it is crucial that I access them in key order. Access won't let me open the Recordset as any type that permits setting the .Index property (which I'd want to set to "PrimaryKey") and warns that the records may be returned in random order if that property isn't set, so I've written a bit of code to compare each key to the one before it, and a few are being reported as showing up out of key order. It seems that both the "/" and the "-" characters are not sorting out in respect to the digit characters the way the ASCII chart suggests they should. The module's OPTION COMPARE statement is presently set to DATABASE, which the help screen says will make comparisons dependent on locale settings, but I can't find anywhere what those settings are. Any suggestions as to how to resolve this?

Here's a specific example. In the following code, the first two lines produce "True" (which basically confirms the ASCII collating sequence) while the last two produce "False".

MsgBox "-" < "/"
MsgBox "/" < "0"
MsgBox "A-19" < "A00-453"
MsgBox "A00-460" < "A00/01-454"

[This message has been edited by AlanS (edited 04-18-2002).]

 

[AlanS]

Upon further reflection, my guess is that the default (and apparently undocumented) comparison protocol removes special characters before comparing two strings, which would account for the "False" produced by the last two lines. For my current project, I'm going to write a byte-by-byte comparison routine to check the key order.

Can anyone confirm this guess, or point me to any documentation that discusses this issue?

 

[Pat Hartman]

If you open a recordset based on a query rather than a table, you can use an order by clause in the query to sort the recordset into key order.

 

[AlanS]

Thanks, Pat. This leaves me with two more questions:

1. If I base the record set on a literal SQL statement, rather than a query, will its ORDER BY clause be in force?

2. Assuming that records are being returned in key order, it seems that key order means (at least in some cases) that case is ignored and special characters are removed from strings. If so, is there any easy way to defeat that and have "key order" mean strict binary order based on the ASCII values of each byte, without exception?

 

[Pat Hartman]

1. Yes
2. I don't see the problem with the special characters. Jet is not case sensitive which is good and bad. I don't know of any way to change that. I entered some values in a table and sorted them and these are the results. I also get false on the last two compares when done in the immediate window so I can't explain why the sort order seems to contradict that. Sorry.

dum1	asciiCode
-	45
/	47
0	48
A-19	65
a-191	97
A00-453	65
A00-460	65
A00/01-454	65