Sorting Dates with query

[psycoperl]

I have what seems to be an easy question I am trying to sort dates in my query however it does not sort chronologically. Can someone please provide guidance as to why this is not working as intended/needed?

Query:

SELECT DISTINCTROW Format$([qryAllData_ByDateSite].[ADate],'mmmm yyyy') AS ADate_Month, 
getfiscalyear([ADate]) AS AYear, getfiscalmonth([ADate]) AS aMonth, 
qryAllData_ByDateSite.Site, 
Sum(qryAllData_ByDateSite.SumOfdelivQty) AS delivQty, 
Sum(qryAllData_ByDateSite.SumOfreadQty) AS readQty, 
Sum(qryAllData_ByDateSite.SumOfpackQty) AS packQty, 
Sum(qryAllData_ByDateSite.SumOfpickQty) AS pickQty
FROM qryAllData_ByDateSite
GROUP BY Format$([qryAllData_ByDateSite].[ADate],'mmmm yyyy'), 
getfiscalyear([ADate]), 
getfiscalmonth([ADate]),
 qryAllData_ByDateSite.Site, 
Year([qryAllData_ByDateSite].[ADate])*12+DatePart('m',[qryAllData_ByDateSite].[ADate])-1
ORDER BY getfiscalyear([ADate]), getfiscalmonth([ADate]);

I am getting:

ADate_Month	AYear	aMonth	Site	delivQty	readQty	packQty	pickQty
April 2012	2012	10	02	38	20	20	20
April 2012	2012	10	12	572	572	572	617
May 2012	2012	11	16	354	354	354	314
May 2012	2012	11	20	561	562	562	526
June 2012	2012	12	02	44	44	44	44
June 2012	2012	12	12	771	771	771	701
February 2012	2012	8	12	249	13	13	13
March 2012	2012	9	16	134	134	134	128
March 2012	2012	9	20	207	207	207	207
July 2012		2013	1	02	34	34	34	34
July 2012		2013	1	12	816	810	810	950
April 2013	2013	10	16	184	184	184	157
April 2013	2013	10	20	125	125	125	125
May 2013	2013	11	02	19	27	27	27
May 2013	2013	11	12	1388	1388	1388	988
June 2013	2013	12	16	83	83	83	173
June 2013	2013	12	20	387	387	387	387
August 2012	2013	2	02	35	35	35	35
August 2012	2013	2	20	311	364	364	364
September 2012	2013	3	02	5	0	0	0
October 2012	2013	4	20	12	12	12	12

 

[xenou]

It appears that getfiscalyear([ADate]) and getfiscalmonth([ADate]) are functions that return text. So you are getting a textual sort order (where, for instance, 10 is less than 9 because it starts with 1).

You could try (might work, not sure):

ORDER BY CLng(Nz(getfiscalyear([ADate]), 0)), CLng(Nz(getfiscalmonth([ADate]),0))

 

[psycoperl]

It appears that getfiscalyear([ADate]) and getfiscalmonth([ADate]) are functions that return text. So you are getting a textual sort order (where, for instance, 10 is less than 9 because it starts with 1).

You could try (might work, not sure):

ORDER BY CLng(Nz(getfiscalyear([ADate]), 0)), CLng(Nz(getfiscalmonth([ADate]),0))

Thank you! That works... Its now in chrono order! Great!

 

[xenou]

No problem. Glad to help.
ξ

 

[james_lankford]

how is April the 10th month ?

ADate_Month    AYear    aMonth    Site    delivQty    readQty    packQty    pickQty
April 2012    2012    10    02    38    20    20    20