Novice getting error on query - please critique

[mo]

(This is a new post to continue an old thread, by the same name.)

Thanks to the help I've rcv'd so far, Ive gotten to this point.

My current code is:
SELECT Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
FROM INVC_DETAIL, CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE
ORDER BY Year(INVC_DETAIL.INVOICE_DATE) ASC,
Month(INVC_DETAIL.INVOICE_DATE) ASC;

The error is:
You tried to execute a query that does not include the specified
expression 'Year(INVC.DETAIL.INVC_DATE)' as part of an aggregate
function.

If the past holds true for the future, it is going to give this error
for each item used in ORDER BY.
What's the best way to address this?

-Mo

 

[Jerry Whittle]

Since it's a totals query, make the Order By identical to the select.

SELECT Format(INVC_DETAIL.INVOICE_DATE, "yyyymm"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE,
Sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE
FROM INVC_DETAIL,
CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY Format(INVC_DETAIL.INVOICE_DATE, "yyyymm"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE)
ORDER BY Format(INVC_DETAIL.INVOICE_DATE, "yyyymm") ;

As Format changes a date to a string, there's no way practical that you can
get May to show up before August. Also with a two-character year, 1999 data
will show up after 2007.