Sum Calculated Text Box on Datasheet

[mikeTTI]

Hopefully someone can answer this question for me: -

Is it possible to sum a calculated text box in a subform in datasheet veiw by using the Sum function in another calculated text box in the footer of that form?

The text box I wnmat to sum multiplies the result of a DLookUp function in another text box by a number from a field in the underlying table. This text box is working fine I just can't sum it.

I can sum any text box bound to a field in the underlying table, just not the calculated text box.

I have also tried this in single and continuous form view, but whatever view I try I just get 'Error!'.

Would be nice to sort this out as I want to display the sum on the main form.

Am I attempting something impossible

 

[boblarson]

Hopefully someone can answer this question for me: -

Is it possible to sum a calculated text box in a subform in datasheet veiw by using the Sum function in another calculated text box in the footer of that form?

The text box I wnmat to sum multiplies the result of a DLookUp function in another text box by a number from a field in the underlying table. This text box is working fine I just can't sum it.

I can sum any text box bound to a field in the underlying table, just not the calculated text box.

I have also tried this in single and continuous form view, but whatever view I try I just get 'Error!'.

Would be nice to sort this out as I want to display the sum on the main form.

Am I attempting something impossible

You need to recreate the calculation in the footer box so like this:

=Sum([Field1]+[Field2]*([Field3]/[Field4]))

And remember to refer to the FIELD name and not the text box name. If they are the same, you should rename the text boxes so Access is clear that you want the field name.

 

[mikeTTI]

Hi Bob,

Thanks for your help.

I am not quite understanding your solution.

The calculated field I am trying to sum multiplies.

[Tonnes]*[Rate]

[Tonnes] is bound to a field in a table, but [Rate] is the reults of a DLookUp function. I can't refer to [Rate] as a field because it is unbound.

If I read your example correctly I would do either:

(A): =Sum([Tonnes])*Sum([rate])

or

(B): =Sum([Tonnes])*Sum(DLookUp etc)

(A) Doesn't work for my data because Sum (tonnes) * Sum (rate) gives a different number than sum(tonnes*rate) because tonnes and rate vary independently (similar to a weighted average).

(B) I haven't tried yet but I think DLookUp will only return 1 value.

Have I understood your example correctly