Sorting a value 2

[oaklandar]

I have a Table (called myTable with field names of id and status) with the following information
in my Access 2000 database:

id       status
1        Review
2        First Phase
3        Completed
4        In Work
5        Quality Control

Anyway to sort it so the Completed records will be last? So the output would show all the status jobs that are in work (Review, First Phase, In Work, Quality Control) before the Completed jobs?

select * from myTable
order by ????

 

[PHV]

Have you tried this ?
select * from myTable
order by IIf(status="Completed",1,0)

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

 

[lespaul]

I would add a third field to myTable called SortOrder

id       status             SortOrder
1        Review                 1
2        First Phase            2
3        Completed              5
4        In Work                3
5        Quality Control        4

and order by that field.

HTH

Leslie

 

[oaklandar]

Thanks that works when I tested it in my Access database.
But when I use it in my Cold Fusion page it doesnt seem to recognize the IIf part.

What is the IIf and can I use different syntax instead of IIf in the SQL to fetch this data in my Cold Fusion page?

 

[PHV]

Sorry, you're in MS Access query forum.
Glad to know my suggestion works in this context.
Sorry to not know how to help you in your context.

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

 

[oaklandar]

Thanks I might have to add the third field.