Help with Order By clause

[betheball]

With the help of IanH I was able to throw together this SQL:

SELECT Month(ReviewDay)&Year(ReviewDay) AS MyMonth, Count(*) AS MyCount, Sum(IIf(SignedOn-SchTime<3 And SignedOff=0,1,0))+Sum(IIf(SchTime-SignedOff<3 And SignedOn=0,1,0)) AS HoursMet FROM Adherence GROUP BY Month(ReviewDay)&Year(ReviewDay) ORDER BY Month(ReviewDay)&Year(ReviewDay) DESC"

As you can see, I am grouping my records by a combination of month and year. Now the problem, I want it to be ordered by the most recent month/year to the oldest. The above lists months 2-9 before months 10-12. I realize it is because the SQL doesn't recognize Month(ReviewDay)&Year(ReviewDay) as a date. Any thoughts on how to tackle this part of the equation?

 

[Jon K]

Since "yyyymm" can be sorted properly, try this:-

SELECT Month([ReviewDay]) & Year([ReviewDay]) AS MyMonth, Count(*) AS MyCount, Sum(IIf([SignedOn]-[SchTime]<3 And [SignedOff]=0,1,0))+Sum(IIf([SchTime]-[SignedOff]<3 And [SignedOn]=0,1,0)) AS HoursMet
FROM Adherence
GROUP BY Month([ReviewDay]) & Year([ReviewDay]), format([ReviewDay],"yyyymm")
ORDER BY format([ReviewDay],"yyyymm") DESC;

 

[betheball]

Very Nice. Can I pick your brains a tad bit more? How could I modify the above query to work in ASP? If I understand correctly, Format is not a valid function in ASP, so I would have to change the above a bit. I could just use the Access Query as my record source, but would like to see if I can accomplish the same results as the above directly in ASP.

 

[betheball]

Nevermind. I should have thought a little before my last post. This works great:

SELECT Month(ReviewDay)&Year(ReviewDay) AS MyMonth, Count(*) AS MyCount, Sum(IIf(SignedOn-SchTime<3 And SignedOff=0,1,0))+Sum(IIf(SchTime-SignedOff<3 And SignedOn=0,1,0)) AS HoursMet FROM Adherence GROUP BY Month(ReviewDay)&Year(ReviewDay), Year(ReviewDay), Month(ReviewDay) ORDER BY Year(ReviewDay) DESC, Month(ReviewDay) DESC;