Dynamic Sort Problem

[BloodshotVandal]

Hi All

I was wondering if someone could help me with a problem I am having. I have the following code in one of my queries.

SELECT tblMain.Category, *
FROM tblMain
WHERE (((tblMain.Category)=[Forms]![frmMain]![cboFilter]))
ORDER BY Switch(isnull(Forms!frmMain!cboSort),tblMain.Reference,
Forms!frmMain!cboSort="Completed",tblMain.DateComp,
Forms!frmMain!cboSort="Started",tblMain.DateStarted,
Forms!frmMain!cboSort="Modified",tblMain.DateLastMod,
Forms!frmMain!cboSort="None",tblMain.Reference);

The problem is that everything is ordered in ascending order, whereas I need the records to be sorted in descending order when ordering by "Completed", "Started" and "Modified".

Please help

Thanks in advance

 

[Brianwarnock]

try DESC at the end of the order by clause.

Brian

 

[BloodshotVandal]

Thanks for responding.
Wouldn't adding DESC at the end of the order by clause sort everything in descending order? What I would like to do is for the query to sort in descending order only when sorting with dates, sorting by everything else should be sorted in ascending order...
Is that possible?

 

[Brianwarnock]

Yes, having looked more closely I see your dilemma. Not able to suggest without experimenting how to get round this and don't have a DB to experiment with.

Brian

 

[Brianwarnock]

Split your Switch into 2 statements, the one that returns NULL will not affect the sort order, so one for dates with DESC the other for CBO isnull and "none"

Brian

 

[Brianwarnock]

As I haven't got a DB to test this I would be interested in the solution you finally come up with, and if my idea works.

Ta

Brian

 

[BloodshotVandal]

Thanks again Brian.
Found a solution, not exactly the one I wanted, but it works...
Copied the query, so I had one for Ascending order and the other for Descending order, then on my form I had a combo box where the user will be able to select which order they want.

 

[Brianwarnock]

I felt pretty sure that an Order By like below would work.

ORDER BY Switch(isnull(Forms!frmMain!cboSort),tblMain.Reference,
Forms!frmMain!cboSort="None",tblMain.Reference),
Switch(Forms!frmMain!cboSort="Completed",tblMain.DateComp,
Forms!frmMain!cboSort="Started",tblMain.DateStarted,
Forms!frmMain!cboSort="Modified",tblMain.DateLastMod) DESC;


Brian

 

[BloodshotVandal]

Hi Brian

Just try out the code you provided, and yes it works. Thanks.