Error Message

[wat3rfall6]

Hello Everybody!
I use Access 2007 + Windows XP.

How can I make a select query and sort the results the way I like and escape the default ascending sort of Access?

For example I give this command:

SELECT names.* FROM names
WHERE address = "Hamilton 20" OR address = "Varick 43" OR address = "Alion 10"
ORDER BY CASE
WHEN address = "Hamilton 20" THEN 1
WHEN address = "Varick 43" THEN 2
WHEN address = "Alion 10" THEN 3
END

It works in SQL but not in Access. It shows the following error message:

Syntax error (missing operator) in query expression CASE
WHEN address = "Hamilton 20" THEN 1
WHEN address = "Varick 43" THEN 2
WHEN address = "Alion 10" THEN 3
END

Which is the syntax error? Any help would be kindly appreciated.

Thanx in advance

 

[vbaInet]

Create an alias field for the criteria and sort under it. So in the query it could look like this:

AddressSort: IIF([Address] = "Hamilton 20", 1, IIF([Address] = "Varick 43", 2, IIF([Address] = "Alion 10", 3,0)))

Then Sort under that.

 

[wat3rfall6]

First of all, thank you very much!

Do you mean that I should create an extra field with the name alias in my table with the numbers I want?

I tried this code:

SELECT names.* FROM names WHERE IIF([Address] = "Hamilton 20, 1" IIF([Address] = "Varick 43", 2, IIF([Address= "Alion 10",3,0)));

but I still get a syntax error message. Could you please be more specific about the whole procedure and the query including the select code?
I am sorry If I ask too much!Thanks again!

 

[vbaInet]

Have a look at this link and you will see where to place it:

http://www.techonthenet.com/access/functions/advanced/iif.php

 

[gemma-the-husky]

why not design as a visual query - then you could look and see what the SQL should look like