Sort by high, medium, low

[claire76]

I am trying to sort a query by prioirty: H, M, L. The only way I can find to sort is ascending or descending which wont give me the right order. It seems like it shouldn't be that complicated...

 

[NOL]

Hi ,

Create a field2 in your query which will show values 0, 1, 2 to correspond to field1 L, M, H .
and sort on that field.

There may be a better way , but this is what I can think of.
Hope it works for u

Gina.

 

[Jon K]

Or you can incorporate Gina's idea of 0,1,2 into the Order By clause of a query using nested iif() as follows:-


SELECT ......
FROM TableName
ORDER BY iif(Priority='H', 0, iif(Priority='M', 1, 2));

 

[Cosmos75]

Thread with the same princliple!
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33851

I actually gave the same solution NOL did! Cool!

 

[claire76]

That worked perfectly in my query. Now however, Im trying to run a report off of the query and the report is not pulling in the order that the query results are. I have set 'Priority' as a one of the hearders. Do I need to code similar logic into the report?

Thanks for all your help!

 

[NOL]

Hi ,

I tried it out and it works fine.
If you wish you can post your mdb and I'll help if I can ..

Gina.

 

[Cosmos75]

Add the number field (the one Gina suggested - where you rank High, Med, Low) in your report. Sort by that field, and set the Invisible property of the field to true (assuming you don't want to show that field but just want to use it to sort the report correctly).