I am an advanced user and I have a form based on a query that I'd like to custom sort in VBA. The only way I know how to do this is with the OrderBy and OrderByOn commands. Yet the OrderBy command only accept parameters and not values, so I can't use a SWITCH/IIF statement like I would in an SQL query.

In short, how do I go about sorting the following values such that anything "CONFIRMED" OR "PENDING" will show up at the top of the list:


CONFIRMED
PENDING
CANCELLED
NOT IN LIST
DUPLICATE

Any help would be appreciated. Thank you in advance.

-Vanesai

---

Answer from Richard Rost:

What I would do is to create a table with your values and their preferred sort order.

1, Confirmed
2, Pending
3, Cancelled
etc.

Then, create a link between your data table and this status table. You can then use the Sort Order field to control which records sort in what order.

Now personally, I like controlling the sort order of FORMS by changing the RecordSource property.

Me.RecordSource = "SELECT * FROM MyTable ORDER BY SortOrderID"

That's just me. :)