(Error 3122) Tried to execute a query part of aggregate function

[CoolFactor]

I receive the following error when try to run this query:

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.

Possible cause:
You did not enter an aggregate function in the TRANSFORM statement.


Here the SQL code that I'm using for this query.
SELECT Sum(qry_DepositsbyQuarter.[Deposits/Receipts]) AS [SumOfDeposits/Receipts]
FROM qry_DepositsbyQuarter
GROUP BY qry_DepositsbyQuarter.Year, qry_DepositsbyQuarter.Expr1
HAVING (((qry_DepositsbyQuarter.Year)=[Forms]![frm_Deposits by Quarter Menu]![List0]) AND ((qry_DepositsbyQuarter.Expr1)=[Forms]![frm_Deposits by Quarter Menu]![List2]));

 

[PaulBricker]

I think if you open the query in design view, and look at the totals line, you will see the Totals line for the last part is set to "Expression". Try changing that to "Where"

your sql will read

SELECT Sum(qry_DepositsbyQuarter.[Deposits/Receipts]) AS [SumOfDeposits/Receipts]
FROM qry_DepositsbyQuarter
GROUP BY qry_DepositsbyQuarter.Year, qry_DepositsbyQuarter.Expr1
WHERE (((qry_DepositsbyQuarter.Year)=[Forms]![frm_Deposits by Quarter Menu]![List0]) AND ((qry_DepositsbyQuarter.Expr1)=[Forms]![frm_Deposits by Quarter Menu]![List2]));


Paul

 

[dhookom]

Move the where condition from the HAVING to the WHERE

SELECT Sum([Deposits/Receipts]) AS [SumOfDepsRecpts]
FROM qry_DepositsbyQuarter
WHERE [Year]=[Forms]![frm_Deposits by Quarter Menu]![List0]) AND [Expr1]=[Forms]![frm_Deposits by Quarter Menu]![List2];

I'm not sure why you had the two columns in the group by when they were not included in the SELECT clause. You should also replace Expr1 with something.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Paul,
I believe the WHERE clause always comes before the GROUP BY clause.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CoolFactor]

When I input what you suggested I receive the following error message.

Syntax error (missing operator) in query expression ‘qry_DepositsbyQuarter.Expr1
WHERE (((qry_DepositsbyQuarter.Year)=[Forms]![frm_Deposits by Quarter Menu]![List0]) AND ((qry_DepositsbyQuarter.Expr1)=[Forms]![frm_Deposits by Quarter Menu]![List2]));

 

[CoolFactor]

Duane,

The reason I had them the SELECT clause is because they reference back to other list boxes.

So if in one list box I select year then list box populates with
Quarters and when I select a quarter the next list box populates all deposits/receipts and another list box populates with total for that quarter of all the deposits/receipts.

 

[dhookom]

What is expr1? Is it a generated column/field in qry_DepositsByQuarter?

If you can't figure this out, reply with the full SQL statement.

You should do yourself a favor and rename List1 and List2. I would not want to have to maintain this application without a clear indication of the purpose of the control in the control's name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PaulBricker]

Thanks Duane, I wasn't paying attention to the structure of the clause. My bad. I'll go back to sleep now.

Paul

 

[CoolFactor]

Duane,

Expr1 equals this

Expr1: "Qtr " & DatePart("q",[Date],1,0)

 

[CoolFactor]

Duane,

I figured it out.

Thank you