Problem Summing Controls in Report Footer

[April15Hater]

I'm just at a loss as to why this isn't working. In a nutshell, I'm trying to sum a control in the report footer. I have a textbox named txtTotal(which is what I'd like to sum) in the Detail section of my report with the following expression in the control source:

=IIf([Function Name]='Approved Hours',
[txtUnit]*DLookUp("[HourlyRate]","tblContractor","[ContractorID] = " & [ContractorID]),
Round(IIf(IsNull([Aerial]),0,(([Aerial]/5280)*DLookUp("[FunctionCost]","tblContractorFunction","[FunctionID] = " & [FunctionID] & " AND [FunctionType] = 'Aerial'")))+IIf(IsNull([underground]),0,(([underground]/5280)*DLookUp("[FunctionCost]","tblContractorFunction","[FunctionID] = " & [FunctionID] & " AND [FunctionType] = 'underground'")))+IIf(IsNull([unit]),0,(([unit])*DLookUp("[FunctionCost]","tblContractorFunction","[FunctionID] = " & [FunctionID] & " AND [FunctionType] = 'unit'")))+IIf(IsNull([setup]),0,(([setup])*DLookUp("[FunctionCost]","tblContractorFunction","[FunctionID] = " & [FunctionID] & " AND [FunctionType] = 'setup'"))),2))

I know it's lengthy, but the good news here is that this part of the report works fine. My problem comes in when I try to sum this text box up. I have another text box in the Report Footer with the following expression:

=Sum([txtTotal])

When I run the report it is not recognizing the textbox and brings up a parameter box for txtTotal.

Any ideas?

Thanks,

Joe

 

[pbaldy]

You can't sum a calculated control. Typically the solution is to simply recreate the calculation within a sum:

=Sum(Price * Quantity)

Given the complexity of yours, I'd probably add a second hidden textbox in the detail that refers to the calculation:

=txtTotal

Set it's running sum property appropriately, and in the report footer:

=NewTextboxName

 

[April15Hater]

You can't sum a calculated control.

Really?!? That sucks! Your solution did the trick (and also introduced me about the running sum property), but I would think that summing a calculated control would be something you could do. Oh well...long live microsoft, lol.

Thanks Paul!

Joe

 

[pbaldy]

No problem Joe. Just found this if you wanted more info:

http://support.microsoft.com/kb/208850

 

[April15Hater]

A solution and an explanation... I can't thank you enough! Makes perfect sense now though. Sheesh, what would i do without AWF?!?