DAO SQL Help. Function Problems

[Brandon Johnson]

I'm having a little bit of problems trying to make my SUM function
work. It throws me a runtime error 3122 stating that "You tried to
execute a query that does not specify the expression 'Rgn_Nm' as part
of an aggragate function. Could anyone please help me. ive tried so
many different avenues. The query that this sql is drawing from is
already set with distinct values, sorted with finding certain upcs
under UPCCase and also is set to find just 0's and blanks in the
sumofinvqty. So if anyone culd help with that that would be great.

Set extract = CurrentDb.OpenRecordset("SELECT SUM([SumOfInvQty]),
Rgn_Nm, CustNbr, DCName, Store_Name, Address, City, State, UPCCase,
ItemCd FROM qryZerosAndBlanksVoids GROUP BY CustNbr;", dbOpenDynaset)

 

[Jeff L]

When you perform a group by statement, you have to include all the
fields from your select statement in the group by statement. So your
statement should be:

Set extract = CurrentDb.OpenRecordset("SELECT SUM([SumOfInvQty]),
Rgn_Nm, CustNbr, DCName, Store_Name, Address, City, State, UPCCase,
ItemCd FROM qryZerosAndBlanksVoids GROUP BY Rgn_Nm, CustNbr, DCName,
Store_Name, Address, City, State, UPCCase, ItemCd ;", dbOpenDynaset)

Hope that helps!

 

[Brandon Johnson]

helped big time. thankyou so much for your help.