Sorting + and - in Access 97 and 2000

[niax]

Does someone know why access 97 sorts + first and then -
and Access 2000 sorts - first and then plus and how I can get access 2000 to sort + and - like access 97

I''ve tested this with a simple table with 1 field:

+
-
+
-

a query sorting this field ascending gives:
Access 97:
+
+
-
-

Access 2000:
-
-
+
+

I want upgrade to Access 2000, but it has to sort + first and the - because it's essencial for my database.

Can someone please help me with this problem.

Thanks,

Alexander

 

[allanon]

Are these all text fields?

If they are then I have a theory.

In access97 these are just text fields and Ascii value of '+' comes before ascii value of '-' therefor your sort order.

If access 2000 is recognizing that the + and - are part of numeric values then it would sort them '-' and then '+'.

This is just a theory.

 

[niax]

This could be a theory. But I really need a solution to solve this problem, without rebuilding my complete database and I have no idee how to do this.

Thanks anyway

 

[MazeWorX]

Sort Descending Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[allanon]

I did a little experimenting and I have found that my above theory is correct.

The way that I got around it is instead of having this in a table:

+
-
+
-

I put this:

+a
-a
+a
-a

and then it sorted into the expected order.

Even if I have this:

+a
-b
+c
-d

It sorts to:

+a
+c
-b
-d

The way that I get around this problem is with a query. The following query:

SELECT Table1.MyField
FROM Table1
ORDER By (Table1.MyField & "a"

returns

+
+
-
-

don't know if it will help but that is what I have found.

 

[niax]

Sorting descending won't work because in the query there also are some value's 01 and 02 and they should be sorted ascending.
The complete query is used to build loop diagrams and the +,-,01,02,...... are terminal rows used in the loop drawing. In a query there are about 600 records and I really need it to sort on +,- and 01 and 02.

 

[PWise]

try
SELECT val(Table1.MyField)
FROM Table1
ORDER By val(Table1.MyField);
and forget about the letters