Sort Date Column

[mattc66 via AccessMonster.com]

How would I get this to sort by month and year. It seem to be grouping the
months together.

ITEM DESCRIPTION QTY SHIPDATE
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 12 01/2007
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 16 01/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 20 02/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 19 04/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 30 07/2007
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 28 08/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 12 10/2007

SELECT qrySalesNumberSau104.ITEM, qrySalesNumberSau104.DESCRIPTION, Sum
(qrySalesNumberSau104.QTY_SHIP) AS SumOfQTY_SHIP, qrySalesNumberSau104.
SHIPDATE
FROM qrySalesNumberSau104
GROUP BY qrySalesNumberSau104.ITEM, qrySalesNumberSau104.DESCRIPTION,
qrySalesNumberSau104.SHIPDATE
ORDER BY qrySalesNumberSau104.ITEM, qrySalesNumberSau104.SHIPDATE;

 

[John Spencer]

It is sorting in alphabetic order since you have a string.

You can change the ShipDate string to have year first and then month.

Or change the order by clause to
ORDER BY qrySalesNumberSau104.ITEM, Right(qrySalesNumberSau104.SHIPDATE,4),
qrySalesNumberSau104.SHIPDATE

Or use the full date to sort by if you have it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

It appears that your field, SHIPDATE, is a text field and not a DateTime
field as it should be.

Also you said you want to 'sort by month and year' and that is what it looks
like it is doing. Maybe you meant by year and month.

Try this --
ORDER BY qrySalesNumberSau104.ITEM, RIGHT(qrySalesNumberSau104.SHIPDATE, 4),
LEFT(qrySalesNumberSau104.SHIPDATE, 2);

 

[Marshall Barton]

How would I get this to sort by month and year. It seem to be grouping the
months together.

ITEM DESCRIPTION QTY SHIPDATE
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 12 01/2007
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 16 01/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 20 02/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 19 04/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 30 07/2007
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 28 08/2008
2025-05 FTG,NIPPLE,1/2 NPT x 3IN, 12 10/2007

SELECT qrySalesNumberSau104.ITEM, qrySalesNumberSau104.DESCRIPTION, Sum
(qrySalesNumberSau104.QTY_SHIP) AS SumOfQTY_SHIP, qrySalesNumberSau104.
SHIPDATE
FROM qrySalesNumberSau104
GROUP BY qrySalesNumberSau104.ITEM, qrySalesNumberSau104.DESCRIPTION,
qrySalesNumberSau104.SHIPDATE
ORDER BY qrySalesNumberSau104.ITEM, qrySalesNumberSau104.SHIPDATE;

It seems as though your ship date field is a Text field. If
it is, then convert it to a real date field by using the
CDate function.