sort query results by calendar month

[sfgiantsdude]

I'm trying to sort my query results by the month value "Data (IMPORT).KP_MM_DS" but I get alphabetical results. Any suggestions?

SELECT [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, Round((Count([Data (IMPORT)].KP_ISSU_ID_NB)/20)) AS CountOfKP_ISSU_ID_NB, [Data (IMPORT)].KP_MM_DS
FROM ([Data (IMPORT)] LEFT JOIN [Facility Lookup] ON [Data (IMPORT)].KP_FAC_NM = [Facility Lookup].[FAC NAME]) LEFT JOIN DEPTLOOKUP ON [Data (IMPORT)].KP_DEPT_ORG_NM = DEPTLOOKUP.[EPM DEPARTMENT NAME]
WHERE ((([Facility Lookup].[REPORTING MCA])<>"XXX") AND (([Data (IMPORT)].KP_SB_RGN_CD)="N"))
GROUP BY [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, [Data (IMPORT)].KP_MM_DS;

[rzw0wr]

try adding month() function to your query and sorting that field.

Dale

[JoeM]

What is the format of your date field? (maybe provide some examples)
Is it actually formatted as a date field or a text field?
Can your data span multiple years where you would need to keep "March 2012" separared from "March 2013"?

[sfgiantsdude]

Hi JoeM

Thanks for your reply.

The field that needs to be sorted by calendar month is "KP_MM_DS" and the values are just month, i.e. "January", "February". It does not need to be concatenated by year or anything like that. It's being grouped by year but it doesn't need to have it combined. Thanks!

[JoeM]

You can create a calculated field that uses your month for sorting purposes, i.e.:

DateSort: DateValue([KP_MM_DS] & " 1, 2000")