Error message 3122. Problem using ‘sum’ on amount field in totals query (1 Viewer)

[KirRoyale]

I have a piece of code where I am selecting values for 3 of the fields through combo boxes to run a query.
This works fine on a detailed query but I have problems when I try and amend the code to run a totals query.
When I run the following query directly, there is no problem:

[FONT=Arial]SELECT Call1CumulativeFULL.[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category, Call1CumulativeFULL.CODE, Call1CumulativeFULL.EnglishDeptName, Sum(Call1CumulativeFULL.[/FONT]料金[FONT=Arial]) AS [/FONT]料金[FONT=Arial]OfSum[/FONT]
[FONT=Arial]FROM Call1CumulativeFULL[/FONT]
[FONT=Arial]WHERE (((Call1CumulativeFULL.Category)="voice") AND ((Call1CumulativeFULL.[/FONT]請求年月[FONT=Arial]2)="2013[/FONT]年[FONT=Arial]4[/FONT]月[FONT=Arial]") AND ((Call1CumulativeFULL.CODE)="08-11101"))[/FONT]
[FONT=Arial]GROUP BY Call1CumulativeFULL.[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category, Call1CumulativeFULL.CODE, Call1CumulativeFULL.EnglishDeptName[/FONT]
[FONT=Arial]ORDER BY Call1CumulativeFULL.[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category;[/FONT]

However, when I try to include similar SQL into my code:

[FONT=Arial]  strSQL = "SELECT Call1CumulativeFULL[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category, Call1CumulativeFULL.CODE, Call1CumulativeFULL.EnglishDeptName, Call1CumulativeFULL[/FONT]料金[FONT=Arial] " & _[/FONT]
[FONT=Arial]           "FROM Call1CumulativeFULL " & _[/FONT]
[FONT=Arial]           "WHERE Call1CumulativeFULL.Category" & strCategory & _[/FONT]
[FONT=Arial]           "AND Call1CumulativeFULL[/FONT]請求年月[FONT=Arial]2" & strMonth & _[/FONT]
[FONT=Arial]           "AND Call1CumulativeFULL.CODE" & strDept & _[/FONT]
[FONT=Arial]           "GROUP BY Call1CumulativeFULL[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category, Call1CumulativeFULL.CODE, Call1CumulativeFULL.EnglishDeptName " & _[/FONT]
[FONT=Arial]           "ORDER BY Call1CumulativeFULL[/FONT]請求年月[FONT=Arial]2, Call1CumulativeFULL.Category;"[/FONT]

I get the error message:

[FONT=Arial]“An unexpected error has occurred. [/FONT]
[FONT=Arial]Please note of the following details:[/FONT]
[FONT=Arial]Error Number: 3122[/FONT]
[FONT=Arial]Description: You tried to execute a query that does not include the specific expression ‘[/FONT]料金[FONT=Arial]’ as part of an aggregate function.”[/FONT]

‘料金’ is the amount’ field on which I selected ‘Sum’ rather than ‘Group by’.
Could somebody please tell me where I’m going wrong? I haven’t been able to find the answer from related posts.

 

[pbaldy]

You don't have Sum around it in the second. You also appear to be missing the period to separate the table and field names.

 

[KirRoyale]

Thank you. If I change it to:

[COLOR=#000000][FONT=Arial]Sum(Call1CumulativeFULL.[/FONT]料金[FONT=Arial]) AS [/FONT]料金[FONT=Arial]OfSum[/FONT][/COLOR]

It works fine!

 

[pbaldy]

Happy to help!