Order by - multiple key descending order

[credo]

I have a web based search page (ASP) that allows users to search for villas within regions and also price.

The user can choose the search results sort order (price, region or villa size) via radio buttons on the search page. If a user selects sort results by Region/Department for example, 2 gets passed to the search results query.

Within the sorted results I show the Villas in ascending order based on Villa ID.

However I'm struggling to make the region/department search results show in ascending order : the order by part of the query is

ORDER BY (iif(varOrder =1 ,Tbl_PriceBands.PriceBand, (iif(varOrder = 2, Tbl_Villas.DepId, (iif(varOrder = 3, Tbl_Villas.SleepsTo)))))) , Tbl_Villas.VillaID ASC



I have tried
ORDER BY (iif(varOrder =1 ,Tbl_PriceBands.PriceBand, (iif(varOrder = 2, Tbl_Villas.DepId ASC, (iif(varOrder = 3, Tbl_Villas.SleepsTo)))))) , Tbl_Villas.VillaID ASC

but this produces an ASP error in the web page - how do I sort this one field into ASCENDING order ?

thx in advance

 

[TonyJollans]

Hi credo,

Isn't ASC the default? If not, you need to have the ASC outside the IIF construct ..

[blue][tt]ORDER BY (iif(varOrder = 1, Tbl_PriceBands.PriceBand,
(iif(varOrder = 2, Tbl_Villas.DepId,
(iif(varOrder = 3, Tbl_Villas.SleepsTo)))))) [highlight]ASC[/highlight],

Tbl_Villas.VillaID ASC[/tt][/blue]

This will make it apply to result of the condition, whatever it is; if that isn't what you want you will have to rearrange the conditions, perhaps like this ..

[blue][tt]ORDER BY iif(varOrder = 1, Tbl_PriceBands.PriceBand),
iif(varOrder = 2, Tbl_Villas.DepId) ASC,
iif(varOrder = 3, Tbl_Villas.SleepsTo),
Tbl_Villas.VillaID ASC[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.