When is "+1233" less than "-1233"

[OldBirdman]

I have a table with a field -- Code -- containing the following values:
+1233
1233
-1233
w-aaa
wcc
wcc'
wcc k
wcc!
wcc,
wcc'a
wcch
wccm
wcc'
w-zzz

These are shown above in Ascending order if ordered with the A|Z button on the Table Datasheet Toolbar. Also this is the order when SQL = SELECT Code FROM Test ORDER BY Code.

Immediate Window ?"+1233" < "-1233" gives me True.

As I can set a database sort order in Tools->Options, with about 30 choices, I presume that Access has tables to convert from ASCII to a sort order differing by language.

Is it possible for me to create and use such a table?

OldBirdman

[ChipR]

Does anyone know where to find the options equivalent to the tools->options in Access 2007? I can't find it at all.

[ChipR]

The only thing I can come up with so far to compare numbers accurately when mixed with strings is to use the IsNumeric() function to sort the numbers from the text and sort them separately. That does handle the "+" correctly. Otherwise I don't see how any string value for + is going to sort +1, 1, +2, 2, +3, 3 correctly, or anything similar.

[OldBirdman]

First, I notice a typo in the "Sorted" list originally presented. The second occurrance of wcc' should be wcc's in the second from bottom item. Sorry.

For true numeric strings, IsNumeric would allow creating a second field in the table and storing the numeric portion in that field. Then the table could be sorted by ORDER BY LeadingNumeric, Code. If my new field -- LeadingNumeric-- were filled by LeadingNumeric = Val(Code), and non-numeric strings had the value 9999999999 to force the alpha data to the end, Unless the numeric were imbedded in the string, i.e. X+1233X, X1233X, X-1233X, then the original problem occurs.

This doesn't address the problems with special characters, nor the fact that I may not want the Upper and Lower case alphabetic characters to be considered identical.

Original question - can I define my own sort order?

[Stewart Ross]

Sorry, OldBirdman, I too am not aware of any way that a custom sort order table can be defined and applied at run-time that would change the way Access sorts alpha-numeric data. When I need to define custom ordering I create calculated fields in the underlying query that reflect the components of the sort, then combine these to create a single custom field that can be sorted using the default alphanumeric sorting.

-Stewart