Probably a simple one this. I have made a query that works fine, and generates a cost per invoice.( price each * quantity).Then I calculate the VAT (tax). The query performs all these tasks. I then created a report, which sets out the invoice as required because sometimes there is more than one item per invoice. I have a Total in the query that totals cost plus tax. All I want to do is create the "summed total" ( all cost and all tax) in the footer. I have placed a text box and used "=sum([Total]) but I keep getting an error. I can't seem to get this one working.

Thank in advance

What does Total total in the query? Cost + tax for each item?

So if you have more than one item in the invoice, you can create a hidden text box next to the Total box, and make the hiddent text box equal the cost + tax, then have a property of running sum over all items for the hidden text box, then all you have to do with the text box in the footer is make it = the hidden text box

Yes thats right, "Total" is Cost plus tax. I should create this hidden box next to the total box on the report itself? I shall try this first thing in the morning and letyou know. Thanks for the reply

You cannot sum a calculated control on a form or report; Access doesn't store the result of the calculation for each record, so when you get to the footer, it doesn't have any idea what to sum up.

You either need to add an expression field to your query that calculates the total (since that calculation is now in the underlying query, the form/report can access it for each and every row), or simply do the following in your form/report's footer:

=SUM([Cost] + [Tax])

(again, assuming COST and TAX are fields in the underlying query).

Note that Coco's suggestion of a hidden running sum box should work on a report (not available on a form).

Thanks for the reply. I have tried both above options. I inserted a text box in the footer, and put in =Sum (------) etc, but i keep geting #error where the Total should be. Any Idea's

Thanks again.

Mike

Here is the strange bit. I played a little and happened to move the text box out of the footer and into the main page. It works there!!. It won't work in the header or footer, but it does in the main page. What am I missing???

THX

Make sure none of the controls on your form/report (textboxes, etc.) are named the same as a field in the underlying query.

I.E., if you have a field in your query named COST, the textbox on the form/report must be named something other than COST (for example, txtCost).

Access does this by default when you drag fields onto a report/form to create the control, so you have to rename them manually.

Again, for this to work, the footer needs to be referring to FIELDS in the underlying query. If you have CONTROLS with the same name, it will look at those instead, and, as noted, you can't sum controls.

By the way, cool photo. Tried all that, looked for duplicate names. Sometmes it's easier with paper and pen. When I put my text box in i just leave it as text 26 or whatever. It makes no difference.

THX

Hmm. Any chance you can zip and post your MDB (or enough of a sample to show the problem)?

If not, what is the exact code you have in your =SUM(...) statement.

Peter,
Thanks for this. Attached is the MDB. In the Invoice query you will see price each, qty, Cost(Price* qty), VAT and Total. All I need is for the report 1 to show the data as ist is already set out but I need a full Invoice total, i.e. the sum of all total and all vat per invoice, in the footer.

Regards.

Mike

The problem is you are attempting to do the sum in the PAGE footer. Only the REPORT footer or a GROUP footer can do this.

Peter,
I managed to work around the problem tday, but thanks for trying to help.

Regards,

Mike