Report Sum()

ginoitalo · November 24th, 2004, 02:22 PM

In my Access Projects Report, I cannot use any aggregate functions, sum, avg in the control source of textboxes unless the control source
is a field from the stored procedure linked to the form, I was able to use these functions on any textbox in regular Access, but now
I get Error messages like "Column ???? does not exist in appropriate rowset" or "Aggregate Functions are only allowed on on output fields of the recordsource"

ie: =Sum( [txt_Detail_Total] )
Should, but doesn't work

Access 2003 ADP Project

jemacc · November 24th, 2004, 07:32 PM

What you can do is create a view with calculation and base your report source from that view

example:

Code:

CREATE VIEW VW_TEST
AS
SELECT     OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal
FROM         dbo.[Order Details]
GROUP BY OrderID

Jaime E. Maccou
Applications Analyst