Descending Sort with Positive and Negative Numbers - Access 2000

[BRIANVH]

For some reason, my report is putting negative numbers at the top of the list in a column that is sorted descending and contains both negative and positive numbers. This is a sales by customer report sorted so the customers with the highest sales come at the top of the report. I modifed the report/queries written by another person to now include credit memos (that show as negative sales). It all seemed straight forward, except now a few customers with net negative sales for the year show up on the top of the report, when they should be at the bottom. Please help if you know of any reason why this may sort funny.

 

[dhookom]

It could be an issue with the data type of the field you are sorting on. If it is decimal or maybe even currency, you may want to convert it to double in the sorting and grouping dialog like:
=CDbl([YourField])

This is just a WAG.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRIANVH]

I did not have any luck with the suggestion to use a CDbl([field name]) function in the sorting and grouping dialog box. I could not get the function to work here (syntax problem?) I did try putting [field name]*[field name] in the sorting and grouping dialog box, but that yielded a sort order similar to using absolute values, when I really need the negatives to be at the end of the sort.

Any other suggestions?

Brian

 

[dhookom]

It would have helped if you had replied with the data type of your field.

Try Allen Browne's page

http://www.allenbrowne.com/bug-08.html

"Incorrect sorting with Decimal fields"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRIANVH]

I believe the data type of the field is decimal, and I don't beleive I can change the type since it is being pulled from an ODBC linked table. When I tried either CCur or CDbl functions, the query starts running but then I get the "This expression is typed incorrectly or it is too complex to be evaluated..." error.
In the sorting and grouping dialog box, in the first row under the field/experssion column, I typed in =CDbl([TotalSales]). The problem I am having certianly seems like that mentioned in the link above. Maybe I am out of luck.

 

[dhookom]

You might want to try a pass-through query where you can perform the data type casting on the server. If this was SQL Server, your PT might have an expression like:
... Convert(float,TotalSales) TotalSales ...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRIANVH]

Thanks Duane, you got me headed in the right direction, and now I found a workaround that seems to work ok. I am now using =[TotalSales]+25000 in the sorting and grouping dialog box, which in this case is large enough to eliminate the negative numbers for sorting purposes and then the report sorts OK. Nulls are at the end of the report instead of the negatives but I can live with that.

 

[traingamer]

Nulls are at the end of the report instead of the negatives but I can live with that.

Then use the NZ function to return a zero (rather than a null) so it sorts how you want.

0 + 25000 = 25000
null + 25000 = null

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill