Novice rcv'g error on query - please critique

[mo]

(This is a new post to continue an old thread)

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

 

[Michel Walsh]

SELECT Year(INVC_DETAIL.INVOICE_DATE),
Month(INVC_DETAIL.INVOICE_DATE) ,
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 Year(INVC_DETAIL.INVOICE_DATE),
Month(INVC_DETAIL.INVOICE_DATE),
CONSIGNMENT_CODES.CONSIGNMENT_CODE

ORDER BY Year(INVC_DETAIL.INVOICE_DATE) ASC,
Month(INVC_DETAIL.INVOICE_DATE) ASC;




Hoping it may help,
Vanderghast, Access MVP

 

[mo]

You are awesome! Thanks.
It works like butter.

-Mo