Sorting by month

[mrbebop]

I have looked and tried a lot of things that I have seen in this forum, but I am not able to make this work. I have a query that I am trying to sort a table field, "Date" by month. The field has a "General" time/date format in the table. This is the last code I tried, but as you can see I am not much with VB. Please see if you can help me. The table name is "Batch Data" and the field name is "Date".
Thanks,
Robert
SELECT Format([Batch Data].[Date],'General Date') AS formDate
FROM [Batch Data]
ORDER BY [Date];

 

[Sergeant]

Try this:

SELECT Format([Batch Data].[Date],'General Date') AS formDate
FROM [Batch Data]
ORDER BY Month([Date]);

Orders by month only...regardless of year.

 

[mrbebop]

That is not working either. Any other ideas?
Thanks,
Robert

 

[neileg]

Date is a reserved word and should not be used a field name. Change this, say to MyDate. Then use Format() to return just the month:

ORDER BY Format(MyDate,"mmm")

 

[brianblenkinsop]

datepart

Have you tried the datepart function using expression builder?
This will just return the month part of the date and you can sort on that field?

datepart(month,DATE)

 

[mrbebop]

I changed the "Date" field to "theDate" as suggested. I am not sure if that helped or not, but I think the idea behind it was sound. I found some other code that was similar to what I wanted to do. I changed the table names and field names to match what I had, and IT WORKED. I am posting the results for others to see. Thanks for the help.

SELECT DISTINCTROW Format$([Batch Data].[theDate],'mmmm yyyy') AS [Date By Month]
FROM [Batch Data], [Batch Data] AS [Batch Data_1]
GROUP BY Format$([Batch Data].[theDate],'mmmm yyyy')
ORDER BY Format$([Batch Data].[theDate],'mmmm yyyy') DESC;