CODEWITHSUNDEEP
sqlms-access
great77
great77

Reputation: 143

ORDER BY not working access

I write a query that generates this output, but the date is not sorted using order by and when I did the same thing in the design view of access. It is doing the same thing. 

SELECT 
    AIRCRAFT.tail_no, FORMAT([op_datetime],"Long Date") AS DayOfOperation, 
    COUNT(AIRCRAFT.Tail_No) AS Total
FROM 
    AIRCRAFT 
INNER JOIN 
    OPERATION ON AIRCRAFT.AC_ID = OPERATION.AC_ID
WHERE 
    (((AIRCRAFT.tail_no)='xxxxxx'))
GROUP BY 
    AIRCRAFT.tail_no, FORMAT([op_datetime],"Long Date")
ORDER BY
    MAX([op_datetime]);

the output result below 

tail_no                 DayOfOperation          Total
xxxxxx              31 October 2016                 1
xxxxxx              31 October 2015                 1
xxxxxx              31 May 2016                     2
xxxxxx              31 March 2016                   1
xxxxxx              31 March 2015                   2
xxxxxx              31 July 2017                    1
xxxxxx              31 July 2015                    2
xxxxxx              31 January 2016                 1
xxxxxx              31 December 2016                1

Upvotes: 2

Views: 2576

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You are not ordering by a date. You are ordering by a string. So, your order by is working correctly. The problem is your data.

I would strongly advise you to fix the column, so it is really stored as a date.

However, datevalue() should work for your query:

order by  max(datevalue(op_datetime))

Upvotes: 2

Related Questions