If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting Numbers
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? |
#2
|
|||
|
|||
Sorting Numbers
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? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jasidebo" wrote: 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? |
#3
|
|||
|
|||
Sorting Numbers
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 Jerry Whittle wrote: 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? 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? |
#4
|
|||
|
|||
Sorting Numbers
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 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jasidebo" wrote: 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 Jerry Whittle wrote: 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? 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? |
#5
|
|||
|
|||
Sorting Numbers
"jasidebo" wrote: 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 Myfield0 ASC, MyField DESC rather than just ORDER BY MyField DESC |
Thread Tools | |
Display Modes | |
|
|