Query not sorting as required by date (1 Viewer)

bd528

Registered User.
Local time
Today, 07:29
Joined
May 7, 2012
Messages
111
Hi all,

I have the following query :-

Code:
SELECT tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"mmm-yy") AS Expr1, Count(tblBrokerages.DateTPIAgreementSigned) AS CountOfDateTPIAgreementSigned
FROM tblBrokerages
GROUP BY tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"mmm-yy")
HAVING (((Count(tblBrokerages.DateTPIAgreementSigned))>0))
ORDER BY tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"mmm-yy");

I would like the results to be sorted by BDM_Name, and then Format([DateTPIAgreementSigned],"mmm-yy").

The query runs fine, but is not sorted by date. For example, for one BDM_Name, the lines run in the following order :-

Dec-16
Jan-17
Nov-16

When viewing the query in Datasheet view, I can filter by date as expected. Not sure if it's relevant, but I'm in the UK.

Any ideas how I can get the date sorting working correctly?

Thanks in advance
 

Minty

AWF VIP
Local time
Today, 14:29
Joined
Jul 26, 2013
Messages
10,355
It is sorting it correctly - unfortunately its alphabetically by the format you have set.
You can add a yyyy-mm grouping and order by that, just don't display it.
 

bd528

Registered User.
Local time
Today, 07:29
Joined
May 7, 2012
Messages
111
You can add a yyyy-mm grouping and order by that, just don't display it.

Works perfectly - thanks Minty
 

Users who are viewing this thread

Top Bottom