Basic Group By Month problem

[Doozer1979]

Hello,

This is a real Access 101 problem i'm having.

I've got two years worth of data, that i need to group first by Year, and then by each month in the year.

The issue i'm having is that the query groups by year no problem, but it then groups each month in alpabetical order (ie; Apr,Aug, dec). How do i get it to group by actual order of months (Jan,Feb,Mar etc)?

SELECT Format([Cri_Cleared_Date], "mmm") AS ClearedMonth, Format([Cri_Cleared_Date], "yyyy") As ClearedYear,sum(Cri_No_Of_Crimes) AS No_of_Crimes
FROM Detections_Formatted
GROUP BY Format([Cri_Cleared_Date], "mmm"), Format([Cri_Cleared_Date], "yyyy")
Order By Format([Cri_Cleared_Date], "yyyy"), Format([Cri_Cleared_Date], "mmm")

 

[raskew]

Assuming that [Cri_Cleared_Date] is a date/time data type, try:

Group By Year([Cri_Cleared_Date]), Month([Cri_Cleared_Date])
Order By Year([Cri_Cleared_Date]), Month([Cri_Cleared_Date])

Bob

 

[Doozer1979]

Thanks bob,

How do i then turn the integar value the month function returns into "mmm"

Do i use the format function? How do i combine that with the Month function?

Thanks for your help

 

[raskew]

Can you post a small example?

Bob