SOS!
I'm trying to sort a date in a query but it's being sorted alphabetically instead. The query is linked to an excel file via a table. In the excel file, the cell format is set as date format but once I grouped the table by month (via query), it's converted to text (that what I think is happening).

This is the SQL:
SELECT DISTINCTROW [T: 1018].[Material Number], [T: 1018].[Material Description], Format$([T: 1018].[Date],'mmmm yyyy') AS [Date By Month], [T: 1018].Type, Sum([T: 1018].Total) AS [Sum Of Total], Sum([T: 1018].Value) AS [Sum Of Value]
FROM [T: 1018]
GROUP BY [T: 1018].[Material Number], [T: 1018].[Material Description], Format$([T: 1018].[Date],'mmmm yyyy'), [T: 1018].Type, Year([T: 1018].[Date])*12+DatePart('m',[T: 1018].[Date])-1
HAVING ((([T: 1018].Type)="Ethylene"));

Appreciate it if u could give a thorough explaination and solution as I not that good with access or SQL.

there's no order by clause in your query

care to elaborate further? how can i rectify it?

Order the Year field Asc, in the query grid

Group By creates an implied sort order since the records need to be sorted to be grouped.

Formatted dates sort in text order so don't format the date in the query. Format it in your form or report after it is sorted. If you are only using a query, then you will need to include an order by clause that orders by the unformatted date field. Your group by seems to have an extraneous field - Year([T: 1018].[Date])*12+DatePart('m',[T: 1018].[Date])-1; I would get rid of it.

And finally, it is poor practice to use names such as Date and Value as column names because they are function and property names respectively. They will cause subtle errors because there are times when they will be misinterpreted by VBA and SQL.

Doesn't ( Year([T: 1018].[Date])*12+DatePart('m',[T: 1018].[Date])-1) return an integer, allowing the query to be ordered ?

It creates an integer but what is the point? It is not the value of the date field and it follows the text version of the date and so is lower in the sort order. By the time this field is evaluated, the damage is already done, the rows are ordered by month name followed by year.

Also, I find sorting by a non-visible field causes extreme confusion to people who are not looking at the SQL and it is especially confusing when the sort by value is calculated.

Thanks. Got it!