Iif in ORDER BY

ulkomalainen,

AFAIK, you can't do this directly in a query.

You can, however based on user input use VBA to set the OrderBY property of the form  that is bound to this query, or modify the recordsource property of that form so that the ordering is based on user input.

How is this query being used?

PS: Make sure there's a space before the ORDER BY

Thanks Guys for quick reply.

The excact result I am trying to achive is user inputs 1 or 2. If the input is 1, the result is the list of properties in increasing order of their rooms (and for the same number of rooms in decreasing order of monthly rent); if the input is 2, the result is the list of properties in decreasing order of their monthly rent.

I am not familiar with VBA and thought it could be done with SQL and searching google I found this

http://www.blueclaw-db.com/accessquerysql/dynamic_order_by.htm

Also, is there a way to run alternating queries based on user input without VBA. I mean if input is q1 is run and for to q2?

I suppose you could have an option group in the form footer which changed the record source of the form from one query to another, depending on the option chosen.  You could come up with a macro to do this, but I recommend some gentle VBA.  You'll like it!

Learn something new every day!

That BlueClaw article definitely shows that this can be done.

Try chaging the syntax by dropping the quotes and semicolons in the IIF statement:

SELECT PropertyforRent.PropertyNo, street+', '+city AS Address, PropertyforRent.type, PropertyforRent.rooms, PropertyforRent.rent
FROM PropertyforRent
ORDER BY Iif([Choose]=1, rooms ASC ,rooms DESC );

That's a little guesswork on my part because the article does not show how to handle the sort order.

However, like Bellone - I would opt for VBA as well.

If rooms is a number:

SELECT PropertyNo, street & ', ' & city AS Address, type, rooms, rent
FROM PropertyforRent
ORDER BY IIF([Choose]=1, rooms, -rooms);

However, with multiple addresses, I expect several will have room 101 -  then what?  

Ok SQL below does what I want but I have feeling that I did it in a stupid way :)

Thank you all for the help, LPurvis pointing out that I can not use SQL syntax in Iif was helpful. About VBA I can see your pint but the idea is to do it with SQL queries. If I had the chance I would use some other database and do whatever I want with PHP with flexibly and elegantly.

SELECT PropertyforRent.PropertyNo, street+', '+city AS Address, PropertyforRent.type, PropertyforRent.rooms, PropertyforRent.rent
FROM PropertyforRent
ORDER BY IIf([Choose]=2,Null,Null), IIf([Choose]=1,rooms,Null) ASC, rent  DESC;

                                              

1:
2:
3:

That's the query you're using?
You say what I suggested was informative regarding the use of expressions... But that you've created your own solution?
And not the one I suggested? It looks like a version of it - but altered enough to make it unreliable or just incorrect. (i.e. I'm unsure why it was altered :-s)

The result Ray mentions can be sufficient if your field is numeric. The intention of what I posted was for any type of field.

Equally I'm unsure what influence the fact that you'd liek to be using PHP to achieve flexibility or elegance. You've already said you want this to be a database consideration. What database were you envisaging that would make this particular process better?
That an SP would allow you to execute a different statement depending upon a parameter?