Sum() txtField not working with stored procedure recordset

[Guest]

Hi

I have a form with a table on it and underneath the table i have to 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 change so that it executes a stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[Guest]

Do you have a field or control called "account balance" ??

 

[Guest]

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the table.

Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Hi

I have a form with a table on it and underneath the table i have to 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 change so that it executes a stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[Guest]

Sorry. I didn't see it in your first mesage. I can't see why it shouldn't
work.

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the table.

Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Hi

I have a form with a table on it and underneath the table i have to 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 change so that it executes a stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[BruceM]

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the
table.

Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Hi

I have a form with a table on it and underneath the table i have to
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 change so that it executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[Guest]

Hi

I is huge and not sure i am allowed to post it ie. work rules but i can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the Account
Balance field I put in the text fields control source =Sum([Account Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records source as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum As
'Account Number' FROM AccountBalances WHERE AccountNum = " & lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the
table.

Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

Hi

I have a form with a table on it and underneath the table i have to
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 change so that it executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[BruceM]

You stated that "there is a field [named Account Balance] in the table and
also a field bound to it it the details part of the form which displays what
is in that field in the table."

You have identified Account Balance is both an alias for AccountBal and a
field name. I don't know if the problem lies there, but it is something I
noticed.

If you put a breakpoint into the code and a watch on Me.RecordSource, do you
get the expected RecordSource?

As I understand it, EXEC is used to run a stored SQL server procedure. If
so, a SQL Server newsgroup, or maybe the Access Queries group, may be a good
place to post.

Hi

I is huge and not sure i am allowed to post it ie. work rules but i can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as
expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the
Account
Balance field I put in the text fields control source =Sum([Account
Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records source
as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum As
'Account Number' FROM AccountBalances WHERE AccountNum = " & lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the
table.

:


Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

Hi

I have a form with a table on it and underneath the table i have to
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 change so that it executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[Guest]

Thanks Bruce, the recordsource is what is expected and i suspect it is maybe
because the recordsource is not a direct SELECT statement the when doing the
Sum it has no SELECT statement to refer to i.e. looks at what the control is
bound to and gets the Sum from the recordsource SELECT statement therefore
EXEC is not allowing it to reference the underlying source data.

A bit of a guess but i will try the queries newsgroup and see if anyone
there has any idea, if it cant be done then i will just calculate it in code.

Thanks
Paul

You stated that "there is a field [named Account Balance] in the table and
also a field bound to it it the details part of the form which displays what
is in that field in the table."

You have identified Account Balance is both an alias for AccountBal and a
field name. I don't know if the problem lies there, but it is something I
noticed.

If you put a breakpoint into the code and a watch on Me.RecordSource, do you
get the expected RecordSource?

As I understand it, EXEC is used to run a stored SQL server procedure. If
so, a SQL Server newsgroup, or maybe the Access Queries group, may be a good
place to post.

Hi

I is huge and not sure i am allowed to post it ie. work rules but i can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as
expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the
Account
Balance field I put in the text fields control source =Sum([Account
Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records source
as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum As
'Account Number' FROM AccountBalances WHERE AccountNum = " & lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it the
details part of the form which displays what is in that field in the
table.

:


Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

Hi

I have a form with a table on it and underneath the table i have to
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 change so that it executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[George Nicholson]

Any chance your stored procedure is returning a Null for an AccountBalance?
That could cause an #Error when you tried to Sum, and it would only take
one.

Thanks Bruce, the recordsource is what is expected and i suspect it is
maybe
because the recordsource is not a direct SELECT statement the when doing
the
Sum it has no SELECT statement to refer to i.e. looks at what the control
is
bound to and gets the Sum from the recordsource SELECT statement therefore
EXEC is not allowing it to reference the underlying source data.

A bit of a guess but i will try the queries newsgroup and see if anyone
there has any idea, if it cant be done then i will just calculate it in
code.

Thanks
Paul

You stated that "there is a field [named Account Balance] in the table
and
also a field bound to it it the details part of the form which displays
what
is in that field in the table."

You have identified Account Balance is both an alias for AccountBal and a
field name. I don't know if the problem lies there, but it is something
I
noticed.

If you put a breakpoint into the code and a watch on Me.RecordSource, do
you
get the expected RecordSource?

As I understand it, EXEC is used to run a stored SQL server procedure.
If
so, a SQL Server newsgroup, or maybe the Access Queries group, may be a
good
place to post.

Hi

I is huge and not sure i am allowed to post it ie. work rules but i can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as
expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the
Account
Balance field I put in the text fields control source =Sum([Account
Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records
source
as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum
As
'Account Number' FROM AccountBalances WHERE AccountNum = " &
lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

:

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it
the
details part of the form which displays what is in that field in the
table.

:


Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million people
on
Google video


:

Hi

I have a form with a table on it and underneath the table i have
to
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 change so that it
executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance]) 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.

Thanks

Paul

 

[George Nicholson]

....and, are you sure the stored procedure is delivering [Account Balance] as
a numeric value? Text values would cause an #Error as well...

Any chance your stored procedure is returning a Null for an
AccountBalance? That could cause an #Error when you tried to Sum, and it
would only take one.

Thanks Bruce, the recordsource is what is expected and i suspect it is
maybe
because the recordsource is not a direct SELECT statement the when doing
the
Sum it has no SELECT statement to refer to i.e. looks at what the control
is
bound to and gets the Sum from the recordsource SELECT statement
therefore
EXEC is not allowing it to reference the underlying source data.

A bit of a guess but i will try the queries newsgroup and see if anyone
there has any idea, if it cant be done then i will just calculate it in
code.

Thanks
Paul

You stated that "there is a field [named Account Balance] in the table
and
also a field bound to it it the details part of the form which displays
what
is in that field in the table."

You have identified Account Balance is both an alias for AccountBal and
a
field name. I don't know if the problem lies there, but it is something
I
noticed.

If you put a breakpoint into the code and a watch on Me.RecordSource, do
you
get the expected RecordSource?

As I understand it, EXEC is used to run a stored SQL server procedure.
If
so, a SQL Server newsgroup, or maybe the Access Queries group, may be a
good
place to post.

Hi

I is huge and not sure i am allowed to post it ie. work rules but i
can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as
expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the
Account
Balance field I put in the text fields control source =Sum([Account
Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records
source
as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum
As
'Account Number' FROM AccountBalances WHERE AccountNum = " &
lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

:

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it
the
details part of the form which displays what is in that field in
the
table.

:


Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million
people on
Google video


:

Hi

I have a form with a table on it and underneath the table i have
to
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 change so that it
executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance])
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.

Thanks

Paul

 

[Guest]

It is not returning null for balance as the data in the details section is
showing the balance as expected and it is also the correct data type. I have
been advised that it is most likely not meant to be able to do this with a
stored procedure in an ADP so will just create a function to calculate it
separate.

....and, are you sure the stored procedure is delivering [Account Balance] as
a numeric value? Text values would cause an #Error as well...

Any chance your stored procedure is returning a Null for an
AccountBalance? That could cause an #Error when you tried to Sum, and it
would only take one.

Thanks Bruce, the recordsource is what is expected and i suspect it is
maybe
because the recordsource is not a direct SELECT statement the when doing
the
Sum it has no SELECT statement to refer to i.e. looks at what the control
is
bound to and gets the Sum from the recordsource SELECT statement
therefore
EXEC is not allowing it to reference the underlying source data.

A bit of a guess but i will try the queries newsgroup and see if anyone
there has any idea, if it cant be done then i will just calculate it in
code.

Thanks
Paul

:

You stated that "there is a field [named Account Balance] in the table
and
also a field bound to it it the details part of the form which displays
what
is in that field in the table."

You have identified Account Balance is both an alias for AccountBal and
a
field name. I don't know if the problem lies there, but it is something
I
noticed.

If you put a breakpoint into the code and a watch on Me.RecordSource, do
you
get the expected RecordSource?

As I understand it, EXEC is used to run a stored SQL server procedure.
If
so, a SQL Server newsgroup, or maybe the Access Queries group, may be a
good
place to post.

Hi

I is huge and not sure i am allowed to post it ie. work rules but i
can
guarantee the SP is correct so will put a very small example here

ALTER PROCEDURE AccountBalanceRetrieval (@AccountNo INT)
AS
BEGIN
SET NO COUNT ON
SELECT AccountBal As 'Account Balance', AccountNum As 'Account Number'
FROM AccountBalances WHERE AccountNum = @AccountNo
END

in the form load i can then put

Me.RecordSource = "EXEC AccountBalanceRetrieval 12345678"

I bound controls (using the control source) to [Account Balance] and
[AccountNumber] in the details section and they perform exactly as
expected
i.e. a list of balances.

Now when I have a text box in my form footer to show the sum of the
Account
Balance field I put in the text fields control source =Sum([Account
Balance]}
but it just shows as #Error.

Before i did it via a stored procedure I would have set the records
source
as

Me.RecordSource = "SELECT AccountBal As 'Account Balance', AccountNum
As
'Account Number' FROM AccountBalances WHERE AccountNum = " &
lngAccountNo

Same SQL, Same field names, same control names execpt this way the
=SUM([Account Balance]) works as expected.

:

What is the stored procedure?

yes, there is a field in the table and also a field bound to it it
the
details part of the form which displays what is in that field in
the
table.

:


Do you have a field or control called "account balance" ??

--
"Loose Change 2nd Edition" has been seen by almost 7 million
people on
Google video


:

Hi

I have a form with a table on it and underneath the table i have
to
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 change so that it
executes a
stored
procedure passing in various paramaters to the procedure. 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 with =SUM([Account Balance])
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.

Thanks

Paul