Summing a field obtained via a stored procedure

[Guest]

Hi, I have posted this on the forms design but was advised someone here may
know the answer.

I have a form with a table on it and underneath the table i have two text
boxes which contain similar to the following =Sum([Account Balance]).

This used to work when the recordsource was set to a full SELECT statment ie

Me.Recordsource = "SELECT * FROM AccountBalances" although the real one was
more complicated than that.

Anyway, the recordsource has since been changed so that it executes a stored
procedure passing in various paramaters to the procedure.

i.e Me.recordsource = "EXEC AccountBalances 12345678"

The SP still returns an Account Balance field and a text box in the data
area of the form is bound to this and shows the correct data.

The problem is the total text box in the footer with =SUM([Account Balance])
in the control source no longer works and just shows #Error in the result.

Can anyone explain what is happening, is it a problem with setting the
source to a SP or is there something i need to do. I can always calculate it
using my own function if need be.

I have come to a conclusion that perhaps the =Sum([Account Balance]) no
longer works because there is no SELECT statement in Me.Recordsource for it
to refer to now i have change it to an EXEC even though the same fields are
returned and the data is clearly correct in the details section of the form.

Any advice on this would be appreciated

Thanks

Paul

 

[Guest]

You don't indicate what type of database you are executing the stored
procedure in. To the best of my recollection, most of the rdbms systems do
not allow spaces in field names. Is there a chance that you should be:

=SUM([Account_Balance])

rather than

=SUM([Account Balance])

Dale

 

[Guest]

Its SQL Server but the column name is an alias anyway, accbal as 'Account
Balance' besides it displays the data in the bound txtbox in the details
section so that cant be the problem, I am going to leave it for now and just
create a function to calculate it.

Thanks for responding though much appreciated.

You don't indicate what type of database you are executing the stored
procedure in. To the best of my recollection, most of the rdbms systems do
not allow spaces in field names. Is there a chance that you should be:

=SUM([Account_Balance])

rather than

=SUM([Account Balance])

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi, I have posted this on the forms design but was advised someone here may
know the answer.

I have a form with a table on it and underneath the table i have two text
boxes which contain similar to the following =Sum([Account Balance]).

This used to work when the recordsource was set to a full SELECT statment ie

Me.Recordsource = "SELECT * FROM AccountBalances" although the real one was
more complicated than that.

Anyway, the recordsource has since been changed so that it executes a stored
procedure passing in various paramaters to the procedure.

i.e Me.recordsource = "EXEC AccountBalances 12345678"

The SP still returns an Account Balance field and a text box in the data
area of the form is bound to this and shows the correct data.

The problem is the total text box in the footer with =SUM([Account Balance])
in the control source no longer works and just shows #Error in the result.

Can anyone explain what is happening, is it a problem with setting the
source to a SP or is there something i need to do. I can always calculate it
using my own function if need be.

I have come to a conclusion that perhaps the =Sum([Account Balance]) no
longer works because there is no SELECT statement in Me.Recordsource for it
to refer to now i have change it to an EXEC even though the same fields are
returned and the data is clearly correct in the details section of the form.

Any advice on this would be appreciated

Thanks

Paul