ASP, sorting database with conditions using multiple columns...
- by Mitch
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!