help on report sort order

[Al Newbie]

I have a report that I am trying to sort based on the value of a checkbox.
Here is the code I have (based on Allen Browne's site)

Select Case frm!chkDesc
Case True 'Descending Value
Me.GroupLevel(0).ControlSource = "Sales1"
Me.GroupLevel(0).SortOrder = True
Me.GroupLevel(1).ControlSource = "Customer"
Case False 'Customer
Me.GroupLevel(0).ControlSource = "Customer"
Me.GroupLevel(0).SortOrder = True
Me.GroupLevel(1).ControlSource = "Sales1"
End Select

This seems to work ok except that when the value in "Sales1" is a negative
it always appears at the top of the report what am I doing wrong?

Thanks

 

[Duane Hookom]

Why would you not expect a negative number to appear before 0 or positive
numbers if the sort order is set to Ascending? If you want to see something
else, perhaps we could help you out with an alternative expression.

 

[Ali]

Thanks for the reply.
If the report is set to Ascending then yes I would want negative numbers to
appear before 0 but my problem is that it doesn't matter whether I set the
SortOrder to True or False the negative is always at the top like so

-1979
0
100

or
-1979
35000
2050
0
Any ideas?

 

[Duane Hookom]

I created a table/query, form, and report with the same names as yours and
the code worked exactly as expected. If the box is checked, the report sorts
with the highest positive numbers first and the most negative numbers last.

Is your Sales1 field numeric or text or calculated in the query? If you view
the datasheet of your report's record source, is the Sales1 field left
aligned or righ aligned?

 

[Al Newbie]

Thanks for the reply
Sales1 is a number field (Decimal 14,2) and the report is based on a table

I am using Access 2003 with a 2000 mdb

Any other ideas?

 

[Duane Hookom]

Again, left or right aligned?

 

[Ken Snell [MVP]]

Also note that, if the field is Decimal type, sorting doesn't work very
well. See Allen Browne's web site for more info:
http://www.allenbrowne.com/bug-08.html

 

[Al Newbie]

It was right aligned.

I have now changed the field type to Double and it seems to work perfectly

Thanks again for your help

 

[Al Newbie]

Thanks - I've changed to double and all is well

Also note that, if the field is Decimal type, sorting doesn't work very
well. See Allen Browne's web site for more info:
http://www.allenbrowne.com/bug-08.html