month-year format

[Guest]

I've written a query to group date by month by extracting the month and year,
however now it sorts like text, Apr-06 comes before Sep-05. Access doesn't
seem to recognize the TO_DATE function. Here's the query
SELECT Avg(tblLPR.Bonus_Per_Acre) AS AvgOfBonus_Per_Acre,
Format(LEASE_DATE,"mmm-yy") AS Lease_Date, tblLOCATIONS.County_ID
FROM tblLPR INNER JOIN tblLOCATIONS ON tblLPR.LPR_No = tblLOCATIONS.LPR_No
GROUP BY Format(LEASE_DATE,"mmm-yy"), tblLOCATIONS.County_ID
ORDER BY Format(LEASE_DATE,"mmm-yy");
How can I get the Month_Year format with a Date type?
Thansk

 

[Darren]

Format returns text, so you are in fact sorting that way.
Try
Order By Year(Lease_Date),Month(Lease_Date)

 

[Guest]

Thanks Darren
Looks like that worked! Now to just get those two group-by fields on the
same axis in a chart ... Wish me luck.