If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding a continuous form
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 |
#2
|
|||
|
|||
Adding a continuous form
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)). -- Maurice Ausum "NevilleT" wrote: 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 |
#3
|
|||
|
|||
Adding a continuous form
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" wrote in message ... 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 |
#4
|
|||
|
|||
Adding a continuous form
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? "Rob Parker" wrote: 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" wrote in message ... 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 |
#5
|
|||
|
|||
Adding a continuous form
Hello NevilleT.
"NevilleT" wrote: 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. -- Regards, Wolfgang |
#6
|
|||
|
|||
Adding a continuous form
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 "Wolfgang Kais" wrote: Hello NevilleT. "NevilleT" wrote: 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. -- Regards, Wolfgang |
#7
|
|||
|
|||
Adding a continuous form
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 "NevilleT" wrote in message ... 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? "Rob Parker" wrote: 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" wrote in message ... 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 |
#8
|
|||
|
|||
Adding a continuous form
Hello NevilleT.
"NevilleT" wrote: 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). -- Regards, Wolfgang |
Thread Tools | |
Display Modes | |
|
|