sum gives #error

[Daniel]

Hi,

On a form, I'm trying to perform a sum over a single field in a
table. There's a control on the form which is bound to that field,
but it is named differently. When I open the form, it shows an #Error
in the summation textbox, but when I change the formula
e.g. from:
=sum(nz([rating]))
to:
=sum(nz([rating],0))
it shows the correct sum. Upon closing, saving, and re-opening the
form (with the 'corrected' formula), it shows #Error again. I can
then change the formula back to the original form and it shows the sum
again. But subsequent opens result in the #Error message.

Any idea what I can do? I tried deleting the textbox and making a new
one, but it was to no avail.

Thanks,
Daniel

 

[Jason Lepack]

Is your form setting the recordsource for that text box in it's open
event?

Because it sounds like something is modifying that after you save it.

 

[Daniel]

I set the recordsource for the form in the load event, but the text
box's source is not changed. I'll experiment along this line of
thought.

Is your form setting the recordsource for that text box in it's open
event?

Because it sounds like something is modifying that after you save it.

Hi,

On a form, I'm trying to perform a sum over a single field in a
table. There's a control on the form which is bound to that field,
but it is named differently. When I open the form, it shows an #Error
in the summation textbox, but when I change the formula
e.g. from:
=sum(nz([rating]))
to:
=sum(nz([rating],0))
it shows the correct sum. Upon closing, saving, and re-opening the
form (with the 'corrected' formula), it shows #Error again. I can
then change the formula back to the original form and it shows the sum
again. But subsequent opens result in the #Error message.

Any idea what I can do? I tried deleting the textbox and making a new
one, but it was to no avail.

Thanks,
Daniel

 

[Daniel]

It seems that the problem is related to two things:
-having a different textbox with an invalid controlsource
-performing a calculation on a continuous form in a control rather
than in the underlying query. This seemed to cause a number of
strange errors, but I'm not really sure why.

Fixing those things made the problem go away, at least for now.

Thanks for your help, Jason.

I set the recordsource for the form in the load event, but the text
box's source is not changed. I'll experiment along this line of
thought.

Is your form setting the recordsource for that text box in it's open
event?

Because it sounds like something is modifying that after you save it.

Hi,
On a form, I'm trying to perform a sum over a single field in a
table. There's a control on the form which is bound to that field,
but it is named differently. When I open the form, it shows an #Error
in the summation textbox, but when I change the formula
e.g. from:
=sum(nz([rating]))
to:
=sum(nz([rating],0))
it shows the correct sum. Upon closing, saving, and re-opening the
form (with the 'corrected' formula), it shows #Error again. I can
then change the formula back to the original form and it shows the sum
again. But subsequent opens result in the #Error message.
Any idea what I can do? I tried deleting the textbox and making a new
one, but it was to no avail.
Thanks,
Daniel