Report custom sort

[bilalo]

I have a table Category with P.K categories.
One of the categories is "Discount", I want a query that displays it at the end of the list.
I run the following query, and it works perfectly: SELECT categories FROM Category ORDER BY IIf(categories<>'Discount',1,2), categories
The problem is when I generate a report that is grouped on categories, I only have 2 choices: with A on top and with Z on top, and there is no place to put a custom sort.


I tried to sort the report query but didn't work.
I also changed the order by property of the report and still didn't work.
I think the sorting that is applied to the group (with A on top) is overriding all my sorts attempts

Any idea on how to apply a custom sort on a report?

[orange]

Reports have their own sorting and grouping functionality.
You could add a field to your table - MySortFld - with values that have meaning to your custom sort.

[bilalo]

Reports have their own sorting and grouping functionality.
You could add a field to your table - MySortFld - with values that have meaning to your custom sort.

So there is no other choice than modifying the table?
I was thinking to add a special character at the beginning of the word "Discount", so that it appears at the end of the list. Any idea about a character that comes after 'Z' in MS Access. I tried many special characters such as .Discount,@Discount,(Discount) but all of them makes it at the top of the list.

[CJ_London]

So there is no other choice than modifying the table?

no, just modify the query to include a sort value - then sort in the report

SELECT categories, IIf(categories<>'Discount',1,2) AS sortValue FROM Category

then in your report sort the sortValue column

there is zero benefit in sorting the query when used in the report, just takes longer since it effectively gets sorted twice

[bilalo]

no, just modify the query to include a sort value - then sort in the report

SELECT categories, IIf(categories<>'Discount',1,2) AS sortValue FROM Category

then in your report sort the sortValue column

there is zero benefit in sorting the query when used in the report, just takes longer since it effectively gets sorted twice

Yes works perfectly. Thanks for the help.