Report in alpha order

[khartoum]

Hi all, i have a query which returns sales in months in the correct (sql below) calendar order but my report stays in aplha order, does anybody know how i can resolve this

SELECT Format([tblsales].[orderdate],'mmm yyyy') AS [Month], Sum(tblsales.VAT) AS SumOfVAT, Sum([quantity]*[tblproducts.productprice]+[vat]) AS total, Sum(tblsales.Discount) AS SumOfDiscount, Sum(tblsales.[Shipping&Delivery]) AS [SumOfShipping&Delivery]
FROM tblProducts INNER JOIN tblsales ON tblProducts.ProductCode=tblsales.ProductName
WHERE (((tblsales.OrderDate) Between [Forms]![frmreports]![datefrom] And [Forms]![frmreports]![dateto]))


GROUP BY Format([tblsales].[orderdate],'mmm yyyy'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, Format([tblsales].[orderdate],'mm')
ORDER BY format([tblsales].[orderdate],'mm');

many thanks

[pbaldy]

The report's sorting has to be set up in Sorting and Grouping, on the ribbon.

[khartoum]

i thought it was that simple, but i have tried; sorting snd grouping in calendar order is not an option on reports so if i'm missing something, pleasegive me the exact solution, i'll be eternally grateful
cheers

[RayMilhon]

Add 2 columns to your query Month([tblsales].[orderdate]) and Year([tblsales].[orderdate]) Sort on those 2 fields but display the formatted field. (You can eliminate the year field if you're 100% sure the date range will always be within the same year)

[pbaldy]

You should be able to use the same expression you use in your SQL.

[RayMilhon]

Unfortunately Paul, That doesn't work as it sorts it via Alpha so the sort comes up April, August, December, feburary, January, July, June, March, May, November, October, September. Unless there's something new in Access 2010. You have to sort by the month Number for it to be correct.and if the report crosses years you have to include the numeric year for the sort.

[RayMilhon]

Forgot to mention that the year should come first in the sort order.

[pbaldy]

Unfortunately Paul, That doesn't work

What exactly doesn't work? If this sorts correctly in the query, it should sort correctly in Sorting and Grouping:

format([tblsales].[orderdate],'mm')

[RayMilhon]

Paul normally I would agree with you. However, it's been my experience that as soon as you put a group or sort in the report. That supersedes the sort in the query. This may have changed in 2010 but that's what I've found in previous versions. In 2007 I had a report for a Hospital. The Report Calculated Admits, Length of Stay By Month, by Healthplan. The query grouped by Healthplan, Month (date formatted as mmm yyyy) with a sort on Month (formated as mm), counted the total admits and and summed the length of stay calculated as days between admit date and discharge date. If the admit date was say feb 27 and the discharge date was march 2 then Feb got 2 days and March 1 because the total length of stay was 3 days. Feb 27, 28, march 1 are counted since discharge was feb 2 that day was not counted. The query worked perfectly and sorted exactly how I wanted it. When I put the query into the report and grouped on healthplan the sort order ended up being as I stated earlier. April, August .... I had to include the numeric month and year and sort within the report on those 2 values to get the report to display correctly.

[pbaldy]

In my experience, you HAVE to set the Sorting and Grouping in the report; it will not necessarily follow the sorting in the query (it may, but I never count on it). That said, I don't think you're following me. The OP's expression from the query will sort on the numerical month, and I simply said to use the same expression in Sorting and Grouping, which should also sort on the numerical month. Yes, I could make a case for adding the year, but if it worked as desired in the query it will work the same in the report.

[RayMilhon]

You're right, I wasn't following you. What was confusing me is that if you look at the poster's query the ORDER BY format([tblsales].[orderdate],'mm') Is not included in the Select clause so it is not available in the report to sort on. Which is what I was trying to get across it has to be included in the select.

[khartoum]

Hi chaps, interesting discussion, i tried asolution as below:
base the grouping on the constructed month number field, Format([tblsales].[orderdate],'mm'). Don't even need field in a textbox on report. Can calculate that value in the query and reference that field name or in the report Group/Sort/Total settings with the Group By on expression.
it works fine but once i cross into the next year, it then sorts by 'mm' - i am assuming Ray that is where the sorting by yyyy first coes in - is that right?
ps thanks for input chaps!

[RayMilhon]

Yep, that's exactly correct.