ASP, sorting database with conditions using multiple columns...

Question

First of all, I'm still working in classic ASP (vbScript) with an MS Access Database. And, yes I know its archaic, but I'm still hopeful I can do this!

So now to my problem:

Take the following table as an example:

PROJECTS

ContactName StartDate EndDate    Complete
Mitch    2009-02-13   2011-04-23   No
Eric    2006-10-01   2008-11-15   Yes
Mike    2007-05-04   2009-03-30   Yes
Kyle    2009-03-07   2012-07-08   No

Using ASP (with VBScript), and an MS Access Database as the backend, I’d like to be able to sort this table with the following logic:

I would like to sort this table by date, however, depending on whether a given project is complete or not I would like it to use either the “StartDate” or “EndDate” as the reference for a particular row.

So to break it down further, this is what I’m hoping to achieve:

For PROJECTS where Complete = “Yes”, reference “EndDate” for the purpose of sorting.

For PROJECTS where Complete = “No”, reference “StartDate” for the purpose of sorting.

So, if I were to sort the above table following these rules, the output would be:

PROJECTS

 ContactName StartDate EndDate   Complete
1 Eric    2006-10-01   2008-11-15*   Yes
2 Mitch    2009-02-13*   2011-04-23   No
3 Kyle    2009-03-07*   2012-07-08   No
4 Mike    2007-05-04   2009-03-30*   Yes

*I’ve put a star next to the date that should be used for the sort in the table above.

NOTE: This is actually a simplified version of what I really need to do, but I think that if I could just figure this out, I’ll be able to do the rest on my own.

ANY HELP IS GREATLY APPRECIATED; I’VE BEEN STRUGGLING WITH THIS FOR FAR TOO LONG NOW!

Thank you!

Answer 1

select *
from Projects
order by iif(Complete = 'Yes', EndDate, StartDate)

Answer 2

Your MS Access query should look something like

SELECT Table1.ContactName, Table1.StartDate, Table1.EndDate, Table1.Complete
FROM Table1
ORDER BY IIf([Complete]="Yes",[EndDate],[StartDate]);