Solved Adding a calculated field to a report, based on a query (1 Viewer)

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
I am working on a subreport with shows the data from a query (qryMaintenanceInvoice). One of the report fields is "Subtotal" which is a calculated field in the query (Material cost x Qty.). This works correctly in the query and the report. The trouble comes when I try to add a calculated field in the footer of the subreport that adds up these subtotals. To do this I have created a field with the control source =Sum([qryMaintenanceInvoice]![Subtotal]) , however when I run the report I get #Error in the field box instead of the sum.

What am I doing wrong?

Mafhobb
 

Minty

AWF VIP
Local time
Today, 20:04
Joined
Jul 26, 2013
Messages
10,355
Try adding the Subtotal control again in the sub footer and in the report properties set the control to running sum?
 

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
Try adding the Subtotal control again in the sub footer and in the report properties set the control to running sum?
I have left the original subtotal control and created a new one with the same control source =Sum(Subtotal) I have set this control to "running sum" over group and over all. Still #Error...
 

Minty

AWF VIP
Local time
Today, 20:04
Joined
Jul 26, 2013
Messages
10,355
Remove the Sum() part just add the Subtotal Field from the query.
 

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
Remove the Sum() part just add the Subtotal Field from the query.
If I change the control source to this: =[TimeSpent]*[ActualCost] and I change the field's Running Sum to "Over Groups" or "Over All", the output in this field not a sum of all the query results, but the value of the last line of the query.

If I do the same but with the control source set as =Subtotal, the result is the same. No sum, just the value of the last line in the query. Same number as above.
 
Last edited:

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
Do I need to somehow group the query results so they can be added?
 

Minty

AWF VIP
Local time
Today, 20:04
Joined
Jul 26, 2013
Messages
10,355
Probabaly yes - normally you sum over a group or section.
With reports its always a little difficult to "see" without actually seeing the report design, which isn't always easy without a sample DB.
 

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
Probabaly yes - normally you sum over a group or section.
With reports its always a little difficult to "see" without actually seeing the report design, which isn't always easy without a sample DB.
I've just uploaded it here. Can you please check it to see if you can find what is happening? I am really at a loss....Hold shift to open it.
 

Attachments

  • V8 Simple.zip
    37.3 KB · Views: 302

Minty

AWF VIP
Local time
Today, 20:04
Joined
Jul 26, 2013
Messages
10,355
Have a look here - yes you need grouping to make it work.
 

Attachments

  • Vacational Renting V8 Simple.zip
    47.3 KB · Views: 313

mafhobb

Registered User.
Local time
Today, 15:04
Joined
Feb 28, 2006
Messages
1,245
I see, and adding the grouping added two more sections, with the new section footer being used for the sum.
Thank you. It would have take me a long time to figure this out!
mafhobb
 

Users who are viewing this thread

Top Bottom