Custom Ordering 1

[alexkeel79]

Is there any way to Order By anything other than alphabetical or numerical? For example, I have a database of issues that can have a level of High, Medium, or Low. Now if I order Ascending my order will go High, Low, Medium. Is there anyway I can force my order to be High, Medium, Low?

 

[PHV]

Something like this ?
ORDER BY IIf(level="High",0,IIf(level="Medium",1,IIf(level="Low",2,3)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

If your field name is "level", try this as the order by clause:

[tt]Order by switch([level]="High",1,[level]="Medium"=2,[level]="Low",3)[/tt]

Roy-Vidar

 

[alexkeel79]

I tried the last option but it didn't work. Does it matter that I am already using the GROUP BY statement in the same query?

 

[RoyVidar]

Yes - I think you'll need to include it in the group by clause too (you really need the Group by?)

Roy-Vidar

 

[alexkeel79]

Actually it did work with the Order by and group by. I just had an error in my code. A ',' instead of a '='.

 

[jdegeorge]

Roy

I gave you a star even if Alex didn't. Nice solution!

Jim DeGeorge

 

[jmob]

I am trying to implement the same thing into one of my queries. I tried adding the 2 variations into the criteria lines, but had no luck. Is there a way to put this into a query?

 

[PHV]

but had no luck
Very informative ...
What have you tried and what happened ?
What do you want to do ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmob]

Neither worked. I tried putting both variations in the design view 'criteria' field under the column I am working with. I kept getting syntax errors with no help. I also tried setting the Form.GroupBy property in the code. That didn't work either.
Is there a way to put the 'group by clause' in the criteria in a Query Design view?
Thanks.

 

[PHV]

Can you please post the SQL code of your query and explain us WHAT DO YOU WANT TO DO ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmob]

I figured it out. You reminded me to do the code in the sql view. I was trying to add the group by clause to my query in the design view. Thanks anyways.