compare/sort order

[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?

 

[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?