Adding a continuous form

[NevilleT]

I have a continuous form and want to put a total in the bottom. As some of
the data fields in the detail can be null, I use =nz([Allowance],0) which
works as expected. The result is either the value in Allowance, or zero.
For the sum I use =Sum(nz([Allowance],0)) Which causes an error. Any
suggestions

 

[Maurice]

Did you place your field in the form footer? Your syntax is correct. It
should be a unbound field with the controlsource set to:
=sum(nz(allowance,0)).

 

[Rob Parker]

In the form's footer, the expression =Sum([Fieldname]) will give the correct
sum (for the fields shown on the form - if you filter the form only the
displayed values are summed) even if some of the records have null in that
field.

I suspect that the error arises because your textbox displaying the
Allowance field data is also called Allowance; since you are trying to sum a
calculated expression (the nz expression), in that case Access doesn't know
whether you are referring to the field or the control. You could - and I
recommend that you should - rename the control, but you can simply remove
the nz expression.

HTH,

Rob

 

[NevilleT]

Hi Guys. Thanks for the input but the problem is still there.
- The sum text box is in the footer
- The detail field is called txtAllowance
- Tried both the original formula plus =sum(txtAllowance)

Any more ideas?

In the form's footer, the expression =Sum([Fieldname]) will give the correct
sum (for the fields shown on the form - if you filter the form only the
displayed values are summed) even if some of the records have null in that
field.

I suspect that the error arises because your textbox displaying the
Allowance field data is also called Allowance; since you are trying to sum a
calculated expression (the nz expression), in that case Access doesn't know
whether you are referring to the field or the control. You could - and I
recommend that you should - rename the control, but you can simply remove
the nz expression.

HTH,

Rob

I have a continuous form and want to put a total in the bottom. As some of
the data fields in the detail can be null, I use =nz([Allowance],0) which
works as expected. The result is either the value in Allowance, or zero.
For the sum I use =Sum(nz([Allowance],0)) Which causes an error. Any
suggestions

 

[Wolfgang Kais]

Hello NevilleT.

I have a continuous form and want to put a total in the bottom.
As some of the data fields in the detail can be null, I use
=nz([Allowance],0) which works as expected. The result is either
the value in Allowance, or zero. For the sum I use
=Sum(nz([Allowance],0)) Which causes an error. Any suggestions?

Check the name of the sum field.
Check the data type of the allowance field.
Check the references of the vba project. Use Tools|references from
the vba editor window. If any references are named "MISSING..." then
replace these broken reference by the correct versions.

 

[NevilleT]

Name of the sum field is txtTotalAllowance
The txtAllowance Format is blank and Decimal Places is Auto. The data type
for Allowance is Currency
There are no missing references

Hello NevilleT.

I have a continuous form and want to put a total in the bottom.
As some of the data fields in the detail can be null, I use
=nz([Allowance],0) which works as expected. The result is either
the value in Allowance, or zero. For the sum I use
=Sum(nz([Allowance],0)) Which causes an error. Any suggestions?

Check the name of the sum field.
Check the data type of the allowance field.
Check the references of the vba project. Use Tools|references from
the vba editor window. If any references are named "MISSING..." then
replace these broken reference by the correct versions.

 

[Rob Parker]

Did you try =Sum([Allowance]) ?

If the textbox was called txtAllowance, and there was no other control named
Allowance on the form, your original expression should have been OK.

=Sum(txtAllowance) will not work; additionally, it will cause #Error to
appear in any other calculated expression textboxes in the form footer. And
perhaps continuing from that, maybe the error is arising from another
calculated textbox in the footer.

I assume (and pardon me for even mentioning it) that Allowance is a numeric
(includes Currency) datatype - you're not, I trust, trying to sum a text
field ;-)

And that's about all I can suggest at the moment,

Rob

Hi Guys. Thanks for the input but the problem is still there.
- The sum text box is in the footer
- The detail field is called txtAllowance
- Tried both the original formula plus =sum(txtAllowance)

Any more ideas?

In the form's footer, the expression =Sum([Fieldname]) will give the
correct
sum (for the fields shown on the form - if you filter the form only the
displayed values are summed) even if some of the records have null in
that
field.

I suspect that the error arises because your textbox displaying the
Allowance field data is also called Allowance; since you are trying to
sum a
calculated expression (the nz expression), in that case Access doesn't
know
whether you are referring to the field or the control. You could - and I
recommend that you should - rename the control, but you can simply remove
the nz expression.

HTH,

Rob

I have a continuous form and want to put a total in the bottom. As some
of
the data fields in the detail can be null, I use =nz([Allowance],0)
which
works as expected. The result is either the value in Allowance, or
zero.
For the sum I use =Sum(nz([Allowance],0)) Which causes an error. Any
suggestions

 

[Wolfgang Kais]

Hello NevilleT.

I have a continuous form and want to put a total in the bottom.
As some of the data fields in the detail can be null, I use
=nz([Allowance],0) which works as expected. The result is either
the value in Allowance, or zero. For the sum I use
=Sum(nz([Allowance],0)) Which causes an error. Any suggestions?

Check the name of the sum field.
Check the data type of the allowance field.
Check the references of the vba project. Use Tools|references from
the vba editor window. If any references are named "MISSING..." then
replace these broken reference by the correct versions.

Name of the sum field is txtTotalAllowance
The txtAllowance Format is blank and Decimal Places is Auto.
The data type for Allowance is Currency
There are no missing references

The only idea that I have is that the control was placed in the wrong
footer (maybe the page footer instead of the form footer).