order by date

[Khartoum]

I have the following just to extract the Sale Month:

Month: Format([tblsales].[orderdate],'mmm')

The months display in alpha order whereby I want them in monthn order for reports, is there a simple way to do this
cheers

 

[MSAccessRookie]

If you do not use a GROUP BY statement, then even though they refer to the same date, the value in the SELECT part, and the value in the ORDER BY part do not need to be treated the same way. You already know that the Function Format() can be used to return the name of the Month, but it can also be used to return the numeric equivelent of the ordinal for the month.

Format([tblsales].[orderdate],'mmm') = "March" (Name)
Format([tblsales].[orderdate],'mm') = 3 (Ordinal)

Add an ORDER BY statement that uses the Ordinal value of the date and see if that gives you what you are looking for.

 

[Khartoum]

Thanks for response but am a little lost by how to incorporate into sql; i have:

SELECT Format([tblsales].[orderdate],'mmm') AS [Month], tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, tblsales.VAT, [quantity]*[tblproducts.productprice]+[vat] AS total, tblsales.Discount, tblsales.[Shipping&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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, tblsales.VAT, [quantity]*[tblproducts.productprice]+[vat], tblsales.Discount, tblsales.[Shipping&Delivery];

how will i change this to obtain the ordinal value and get the months in calendar order

Regards Kh

 

[MSAccessRookie]

Thanks for response but am a little lost by how to incorporate into sql; i have:

SELECT Format([tblsales].[orderdate],'mmm') AS [Month], tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, tblsales.VAT, [quantity]*[tblproducts.productprice]+[vat] AS total, tblsales.Discount, tblsales.[Shipping&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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, tblsales.VAT, [quantity]*[tblproducts.productprice]+[vat], tblsales.Discount, tblsales.[Shipping&Delivery];

how will i change this to obtain the ordinal value and get the months in calendar order

Regards Kh

Insert the following after the end of the GROUP BY statement, and watch what happens.

ORDER BY Format([tblsales].[orderdate],'mm')

 

[Khartoum]

Many thanks for help, worked a treat - if you're a rookie, hate to think what I am kh

 

[Khartoum]

Strange but the order has reverted back? here is sql that worked yesterday:

SELECT Format([tblsales].[orderdate],'mmm-yy') 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-yy'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice
ORDER BY Format([tblsales].[orderdate],'mmm-yy');

not sure why?

 

[MSAccessRookie]

Strange but the order has reverted back? here is sql that worked yesterday:

SELECT Format([tblsales].[orderdate],'mmm-yy') 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-yy'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice
ORDER BY Format([tblsales].[orderdate],'mmm-yy');

not sure why?

Unless this is a type-o, the ORDER BY statement is still using the Alpha version of the Month "mmm" as opposed to the numeric version "mm"

 

[Khartoum]

Yes, but because the select and group by where mmm when I attempted to order by and format mm, I kept getting an aggregate error?

 

[MSAccessRookie]

Yes, but because the select and group by where mmm when I attempted to order by and format mm, I kept getting an aggregate error?

That should not make any difference. It works in a test case for me. Show us the entire SQL Code so we can see if there is anything else going on.

 

[Khartoum]

The whole SQL is as below (same as above I think)!

SELECT Format([tblsales].[orderdate],'mmm') 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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice
ORDER BY Format([tblsales].[orderdate],'mmm');

 

[Khartoum]

I changed it to 'mm' which returns in the right monthly numeric order but I need it to display the month on the reports?

 

[MSAccessRookie]

I changed it to 'mm' which returns in the right monthly numeric order but I need it to display the month on the reports?

As I pointed out in Post #4, you are able to use BOTH formats at the same time, and in this case, you need to do so.

You need to use Format([tblsales].[orderdate],'mmm') in the SELECT statement to get the name of the Month to display, and use Format([tblsales].[orderdate],'mm') in the ORDER BY statement, to sort them in the order that you want

 

[Khartoum]

I now have:

SELECT Format([tblsales].[orderdate],'mmm') 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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice
ORDER BY Format([tblsales].[orderdate],'mm');
but get the error:

you tried to execute a query that does not include the specified expression 'Format([tblsales].[orderdate],'mm')' as part of an aggregate function - do not understand what the error means
thanks for your patience - this is the last bit of my database!

 

[MSAccessRookie]

I now have:

SELECT Format([tblsales].[orderdate],'mmm') 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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, Format([tblsales].[orderdate],'mm')
ORDER BY Format([tblsales].[orderdate],'mm');
but get the error:

you tried to execute a query that does not include the specified expression 'Format([tblsales].[orderdate],'mm')' as part of an aggregate function - do not understand what the error means
thanks for your patience - this is the last bit of my database!

It turns out that you need to GROUP BY the date in the second format as well (see above). Make the change marked in RED and it should work.

 

[Khartoum]

Yes, that works, thanks but strangely enough the reports are still in alpha order? As they are a summary report and has a grouping level it automatically defaults the sort to A to Z. PS have just ordered my SQL book!

 

[Khartoum]

Hi all, has anybody got an answer to this - MS rookie has kindly been helping me so the query now reports the months in calendar order but my report still is in alpha order, any quick fix to this; my sql as below:

SELECT Format([tblsales].[orderdate],'mmm') 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'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, Format([tblsales].[orderdate],'mm')
ORDER BY Format([tblsales].[orderdate],'mm');

cheers