IIF expression

[James]

I am trying to write an expression in a query to evaluate if the value in the
field is high, medium or low to return the value 1, 2, and 3 respectively.
when i write the expression in the following manner, the expression always
returns 0.

Priority Sort: IIf([Data Project Names]![priority]="high",1,0)

Reason i am doing this i am trying to sort my list of projects at work into
high, medium and low priority and using sort ascend will prioritize low over
medium due to the way the alphabet is written. is there another sort
function that can avoid this query expression above all-together.

thanks

 

[Lynn Trapp]

Give this a try:

Priority Sort: IIF([Priority] = "High", 1, IIF([Priority] = "Medium", 2,3)

 

[Sunny]

It worked for me

SELECT columns, IIf(priority='high',1,IIf(priority='medium',2,3)) AS numprio
FROM yourtablename
ORDER BY IIf(priority='high',1,IIf(priority='medium',2,3))

make sure you have 'high', 'medium' and 'low' spelled out correct or you
would get 0.