Sorting Numbers

[jasidebo]

I just ran across something strange. I am sorting approxematley 80k records
by a field containing positive and negative numbers. When I sort in
ascending order all is good, however, when I sort in descending order it
sorts the negitive numbers first (high to low) then sorts the positive
numbers (high to low). Any ideas?

 

[Guest]

Very strange indeed.

1. Is it a number field or a text field that is holding numbers?

2. Where are you sorting: table, query, form, or report?

3. If a query, please post the SQL. Maybe you are sorting on more than one
field.

4. Have you done a compact and repair lately?

5. Really grasping at straws: Under Tools, Options, General Tab, what does
it say about Sort Order? General is normal.

6. What version of Access and are all the latest updates and service packs
installed?

 

[jasidebo]

Thanks for the response Jerry. The field is a number field. At first I
performed the sort function in a query with multiple filter criteria. Once I
noticed the problem I tried sorting just that field with no success. I than
tried to sort the field in the applicable table with the same outcome. I am
using Access 2000. The sort order is general and I have not done a compact
and repair lately. The strange thing about this is that the function works
properly one way and not the other.

Jim

 

[Guest]

I'd try the compact and repair. If that doesn't fix it, try creating a new
database and importing that table into it. If it still messes up, I'd sure
like a copy of that new database file!

whittlej at charter dot net

 

[Gary Walter]

I just ran across something strange. I am sorting approxematley 80k
records
by a field containing positive and negative numbers. When I sort in
ascending order all is good, however, when I sort in descending order it
sorts the negitive numbers first (high to low) then sorts the positive
numbers (high to low). Any ideas?

Sure sounds like your "number" field is Decimal...

http://support.microsoft.com/?id=837148

If so, one trick from Michel (besides KB workaround):

ORDER BY Myfield>0 ASC, MyField DESC

rather than just

ORDER BY MyField DESC