Sum of a Calculated Field (1 Viewer)

[PicassoB]

Could some one please help, just spent some time looking through all the posts regarding this , but still cannot get this simple sum to work
I have a continuous form and require a field to give the total sum of the retail cost attached is a Jpeg of the form and code in the control source
please help what am I missing?

 

[vbaInet]

Does it show #Error or #Name when you open the form? Do all those fields exist?

You haven't grouped your calculations so you will not get your expected output.

 

[Dairy Farmer]

You need to use the name of the text box in your formula.

That is:
If the name of the text box in Detail is "RetailCost", the formula in Footer should read
=sum([RetailCost])

The way you have written it, the Sum is looking for [NoItems] and [Price].

 

[vbaInet]

You need to use the name of the text box in your formula.

That is:
If the name of the text box in Detail is "RetailCost", the formula in Footer should read
=sum([RetailCost])

That is incorrect. You can't perform a Sum() on a derived field. The field must exist in the Record Source.

 

[Dairy Farmer]

@ vbaInet

Ah I see.

I normally put the calculation in the Query not the form. The form's data source is the Query not the Table. Therefore the sum() comes from the line total field not the calculation directly.

 

[vbaInet]

That's correct DairyFarmer. So these aggregate functions must refer to a field in the source and not a textbox name for it to work.

 

[Dairy Farmer]

So would you rather calculations are done in a Query than in a Form? Better database structure?

I would tent to go for a Query where there are a lot of records, as I would have some kind of filter on the data (i.e. only data for a certain month), as apposed to a Form that contains all records from a Table.

 

[PicassoB]

Hi guys thanks for the responce - the problem is that, as it is written I get the #error message - when I put in the field name as suggested the total is £0.00

also I am using access 2003 -

 

[Dairy Farmer]

Here is a sample using both Query method and Direct Table method.

Are you try to add 10%?

If so then the code sould read:

([NoItems]*[Price])*1.1

 

[PicassoB]

many thanks DF works perfect - simple when you know how, I appreciate the help from the experts

 

[vbaInet]

So would you rather calculations are done in a Query than in a Form? Better database structure?

I would tent to go for a Query where there are a lot of records, as I would have some kind of filter on the data (i.e. only data for a certain month), as apposed to a Form that contains all records from a Table.

It depends on what I'm doing and the kind of calculation I'm performing.

* If I want to aggregate the field at some point, then I will do it in the query.
* If I want to use the query on several reports or forms and not all of them require the calculated field, I will do it in the form or report that need it.

A form will perform the calculations on the records that are currently visible or fit in the sreen so if the form is set to Single then it will only calculate on the currently viewed record. If it's a datasheet or continuous form, it will calculate on the visible records. Although, there are times when controls with calculated values causes a form to flicker especially if it contains a tab control.

A query will perform the calculations on a good number of records. So if you have 1000 records and you can see 30 records when you open the query in datasheet view, the calculations won't be performed on just the 30 records, it will do it on probably at least 50% of your records. Definitely more than the 30 that are visible. I'm not sure the exact numbers or percentage.

So these are the things to bear in mind.