MTD and YTD query Error 3122

[josros60]

When i run a query I get error 3122, you tried to execute a query that does not specified the expression "TransactionDate" as part of an aggregate function.

sql View:

SELECT Transactions.TransactionDate, Transactions.TransactionID, Transactions.EmployeeID, [Transactions Details].TransactionID, [Transactions Details].CodeID, [Transactions Details].Amount, CodesCategory.CodeCategoryID, [B][COLOR=red]Sum(Abs(Format([TransactionDate],"yyyymm")=Format(Date(),"yyyymm"))*[Amount]) AS MTDRev[/COLOR][/B]
FROM CodesCategory INNER JOIN ((Transactions INNER JOIN [Transactions Details] ON Transactions.TransactionID = [Transactions Details].TransactionID) INNER JOIN Codes ON [Transactions Details].CodeID = Codes.CodeID) ON CodesCategory.CodeCategoryID = Codes.CodeCategoryID
WHERE (((Transactions.TransactionDate)>=[forms]![ReportDateRange]![BeginDate] And (Transactions.TransactionDate)<=[forms]![ReportDateRange]![EndDate]));

Thank you

 

[boblarson]

Go into the query in design view and, for that field, change the GROUP BY to EXPRESSION.

 

[josros60]

I changed to expression by when i run it it shows "0" amount for field where there's an amount for march 2011.

here the new sql view after the changes:

SELECT Transactions.TransactionDate, Transactions.TransactionID, Transactions.EmployeeID, [Transactions Details].TransactionID, [Transactions Details].CodeID, [Transactions Details].Amount, CodesCategory.CodeCategoryID, Sum(Abs(Format([TransactionDate],"yyyymm")=Format(Date(),"yyyymm"))*[Amount]) AS MTDRev
FROM CodesCategory INNER JOIN ((Transactions INNER JOIN [Transactions Details] ON Transactions.TransactionID = [Transactions Details].TransactionID) INNER JOIN Codes ON [Transactions Details].CodeID = Codes.CodeID) ON CodesCategory.CodeCategoryID = Codes.CodeCategoryID
WHERE (((Transactions.TransactionDate)>=[forms]![ReportDateRange]![BeginDate] And (Transactions.TransactionDate)<=[forms]![ReportDateRange]![EndDate]));

This has been driving me crazy i have been trying different ways and never worked until this now at least i got not error but the fields is 0.

thank you again

 

[xenou]

This is a tortuous expression anyway:

Sum(Abs(Format([TransactionDate],"yyyymm")=Format(Date(),"yyyymm"))*[Amount]) AS MTDRev

Your query already includes a date parameter:

WHERE 
	(
		(
		(Transactions.TransactionDate)>=[forms]![ReportDateRange]![BeginDate] 
		And 
		(Transactions.TransactionDate)<=[forms]![ReportDateRange]![EndDate]
		)
	)

Why isn't that enough?