Problem with using SUM() in subform Report Footer

[Linda]

Hello all,


i am having problems with this payment report after i upsized to adp.
The following is a stored procedure that the report is using:



Alter PROCEDURE sp_RptPayments @LoanID int
AS
Select * from rptPaymentsView
where Loan_ID = @LoanID


Then it has a subform that is using the following sp:


Alter PROCEDURE sp_rptSubPayments (
@startingDate datetime,
@endingDate datetime)

AS
Select *
FROM qryPaymentsSubView
WHERE Client_Pmt_Date BETWEEN
@startingDate AND
CONVERT(char(8),@endingDate,112)


I have this field, Sum([ClientAmt]-[PerDebitTransactionFee]-[AddDebitFee])in
the report footer, and that is what's causing the problem. Funny thing
is, i have another instance of the same calculation in the subform and
that is working just fine.


The subform is pulling up a list of payments that belong to a loan#.
The error message that comes up says:

"The expression Sum([ClientAmt]-[PerDebitTransactionFee]-[AddDebitFee])
is invalid aggregatge functions are only allowed on output fields of
the record source @1 @ 1 is invalid. Aggregate functions are only
allowed on output fields on the record source"



Any ideas/comments would be appreciated on this matter..i read
somewhere in this group that the best thing to do is to use a
calculated column, but how do i do that in my situation? Thanks in
advance!

 

[Joerg Schmidt]

Hello Linda,

i have the same Problem. Sometimes it helps, to delete the
field with the SUM() - save the Form and enter a new Field
with the Formula.
But it's no solution for me, because it always happens,
when i update the data on the SQL-Server. No changes at
the Frontend..

I have no idea why, sorry

Greetings
Joerg

 

[Steve Jorgensen]

Does the control have the same name as one of the fields in the calculation?
That's the most frequent cause of problems with calculated controls in general.

 

[Tom Vianese]

I used the same syntax as you
Sum([ClientAmt]-[PerDebitTransactionFee]-[AddDebitFee])
and found that if I distributed the sum function, the claculation would
compute:

Sum([ClinetAmt])-sum([PerDebitTranscationFee])-sum([AddDebitFee])

The first expression works in an Access mdb file, the seond in an Access
project.

Does the control have the same name as one of the fields in the calculation?
That's the most frequent cause of problems with calculated controls in general.

Hello all,


i am having problems with this payment report after i upsized to adp.
The following is a stored procedure that the report is using:



Alter PROCEDURE sp_RptPayments @LoanID int
AS
Select * from rptPaymentsView
where Loan_ID = @LoanID


Then it has a subform that is using the following sp:


Alter PROCEDURE sp_rptSubPayments (
@startingDate datetime,
@endingDate datetime)

AS
Select *
FROM qryPaymentsSubView
WHERE Client_Pmt_Date BETWEEN
@startingDate AND
CONVERT(char(8),@endingDate,112)


I have this field, Sum([ClientAmt]-[PerDebitTransactionFee]-[AddDebitFee])in
the report footer, and that is what's causing the problem. Funny thing
is, i have another instance of the same calculation in the subform and
that is working just fine.


The subform is pulling up a list of payments that belong to a loan#.
The error message that comes up says:

"The expression Sum([ClientAmt]-[PerDebitTransactionFee]-[AddDebitFee])
is invalid aggregatge functions are only allowed on output fields of
the record source @1 @ 1 is invalid. Aggregate functions are only
allowed on output fields on the record source"



Any ideas/comments would be appreciated on this matter..i read
somewhere in this group that the best thing to do is to use a
calculated column, but how do i do that in my situation? Thanks in
advance!