: Trouble getting a calculated field to SUM

[Larry G]

I hope I have posted in the right forum, if not I apologize and would appreicate someone letting me know the correct forum.

Otherwise here is the problem I need help with:

I'm working to develop a form that has several subforms.

Among the fields one of the subforms which is a continuous form I have the following fields:

[cube] - its control source is set to a text field in another subforms footer that is an unbound text box that sums the total cube feet associated with a particular record.

[rate] – is a field in the same query that drives the subform that it is on – [rate] format is currency and holds a dollar amount per cubic foot



[MiniumCharge] is a field in the same query that drives the subform its format is also currency and it holds a dollar amount associated with minimum billing if the [rate] x [cube] is less than the minimum. In other words if [rate]*[cube] is less that the mimimum the [billingamount] is the minimum, else it [rate]*cube].

[BillingAmount] field's control source is “ =IIf([CUBE]*[RATE]<[MinimumCharge],[MinimumCharge],[Cube]*[Rate])”

All of the above seems to work correctly. However:

I have an Unbound Text Box: in the same Subform's footer: control source is set to:

=Sum(Nz([BillingAmount],0))

The above returns $0.00

I have tried other combinations of the sum expression. =Sum([BillingAmount]) returns a blank field

I really need the sum of the [BillingAmount] as its result has to be used/display in other parts of the dbase.

I hope someone can help me to get the [BillingAmount] field to total.

Thanks in advance for any suggestions or comments.

Larry G.

[pkstormy]

Move your BillingAmount control source equation into the query. You should then be able to sum on the BillingAmount field since it is a recognized field from within the recordsource. Otherwise use another query to do the summing and utilize the dlookup expression for the control source of the summed fieldname. Forms/Reports don't typically like summing on another calculated field on that same form or report.

For subforms though, putting the equation into the recordsource may make the query non-updatable. You may need to simplify part of the equation to keep the recordsource query updatable. If that doesn't work and you need it updatable, it's probably best to use vba code and a function to return the values or use lookup type queries to get the summed type values and utilize the dlookup expression for that field's control source on the form.

[qa12dx]

don't use null fields. set the field default value to 0. on the existing table run a update query to set null values to 0, new records will auto gen 0.

now sum away

[Larry G]

THANKS VERY MUCH....I followed your suggestion which solved my issue and allowed me to move on.