Sort by Month in a query

[Eric]

Hi all,

I have my query set up, but I want it to sort by month and
display as January 2004, Feburary 2004, March 2004, etc.
Is there a way to do this? I apologize for the short
questuon. My query is below.

SELECT DISTINCTROW qryEric.Name, qryEric.ProductCode,
Format$([qryEric].[TestDate],"mmmm yyyy") AS [TestDate By
Month], Avg(qryEric.Weight) AS [Avg Weight]
FROM qryEric
GROUP BY qryEric.Name, qryEric.ProductCode,
Format$([qryEric].[TestDate],"mmmm yyyy"), Year([qryEric].
[TestDate])*12+DatePart("m",[qryEric].[TestDate])-1
ORDER BY Format$([qryEric].[TestDate],"mmmm yyyy"), Year
([qryEric].[TestDate])*12+DatePart("m",[qryEric].
[TestDate])-1;

 

[Marshall Barton]

I have my query set up, but I want it to sort by month and
display as January 2004, Feburary 2004, March 2004, etc.
Is there a way to do this? I apologize for the short
questuon. My query is below.

SELECT DISTINCTROW qryEric.Name, qryEric.ProductCode,
Format$([qryEric].[TestDate],"mmmm yyyy") AS [TestDate By
Month], Avg(qryEric.Weight) AS [Avg Weight]
FROM qryEric
GROUP BY qryEric.Name, qryEric.ProductCode,
Format$([qryEric].[TestDate],"mmmm yyyy"), Year([qryEric].
[TestDate])*12+DatePart("m",[qryEric].[TestDate])-1
ORDER BY Format$([qryEric].[TestDate],"mmmm yyyy"), Year
([qryEric].[TestDate])*12+DatePart("m",[qryEric].
[TestDate])-1;

I think this will do what you want:

SELECT [Name], ProductCode,
Format$(TestDate,"mmmm yyyy")
AS [TestDate By Month],
Avg(qryEric.Weight) AS [Avg Weight],
FROM qryEric
GROUP BY [Name], ProductCode,
Format$(TestDate,"mmmm yyyy"),
Format$(TestDate. "yyyymm")
ORDER BY Format$(TestDate. "yyyymm")

 

[Guest]

Thank you! This works perfect. I was hoping that the
formatting that I did (actually, you did) would transfer
over to a report, which isn't the case. How can I get the
exact same thing to appear on the report?

 

[Marshall Barton]

Thank you! This works perfect. I was hoping that the
formatting that I did (actually, you did) would transfer
over to a report, which isn't the case. How can I get the
exact same thing to appear on the report?

If you're using the query as the record source of a report,
then you do not want to sort the query. Use the report's
Sorting and Grouping window (View menu) instead.

Just include the sorting string in the query's field list:

SELECT [Name], ProductCode,
Format$(TestDate,"mmmm yyyy")
AS [TestDate By Month],
Avg(qryEric.Weight) AS [Avg Weight],
Format$(TestDate. "yyyymm") As SortField
FROM qryEric
GROUP BY [Name], ProductCode,
Format$(TestDate,"mmmm yyyy"),
Format$(TestDate. "yyyymm")