Sorting

[Ciaran]

I want to create a query which sorts results based on two fields. I want to sort by Project Status first and then by Project Number.

Within the first field, project status, which is either open, pending, halted and closed, I want to order them in the above order. How can I do this?

Any help will be greatly appreciated.

Ciaran

 

[dcx693]

Obvioiusly sorting by "open", "pending", "halted" and "closed" will not get them into the proper order since they are not in alphabetical order. You can create a calculated field in your query that assigns each record a numeric value based on their text value, then sort on that field. You can assign a 1 to "open", 2 to "pending", 3 to "halted" and 4 to "closed". You could write a complicated IIf function, but you could also use the Access Switch function for a more succinct solution.

You may also want to consider changing the structure of your data a bit so that the project status is stored as a numeric value. It uses up less memory if you just need to store a 1 instead of "Open", etc....but it may not be worth it just to do that for sorting.