Sum of calculated fields in report

[eddyfuente]

This Works:
I have a report with subreports that provide totals to the main report. The main report is grouped to provide the totals by customer. Here is the format:

Customer Labor Materials LineTotal
Cust1 $100 $10 $110
Cust2 $200 $20 $120
Cust3 $300 $30 $130

[Labor] - ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],[rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0)

This doesn't work:
I am trying to create totals for each field (i.e. LaborTotal = $600, see below)
Customer Labor Materials LineTotal
Cust1 $100 $10 $110
Cust2 $200 $20 $220
Cust3 $300 $30 $330
Totals $600 $60 $660


I've tried the following each resulting in #Error:

• ControlSource =Sum(IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],[rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0))
• ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],Sum([rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall]),0)
• ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],Sum(Nz([rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0),0)

Any ideas? This is driving me crazy

 

[milkman2500]

My reports are usually derived from queries. In the queries I typically use formulas that check for null or zeroes such as the following:

iif(IsNull([table_name].[field_name]) or [table_name].[field_name]=0,0,[table_name].[field_name])

However, if its a formula I'll use the NZ formula:

NZ([table_name].[field_name_1]*[table_name].[field_name_2],0)

These formulas also work in reports though. Play around with these and let me know.

I've also noticed in reports that where your information is stored versus where you're totalling plays an important role. For example, if your data is in the "Detail" section of the report and you're trying to total at the "Page Header" or "Field Header" sections may have different results.

 

[eddyfuente]

Thanks for the response...

It's not an issue of null values because they are calculated in the queries. The issue is my subforms are based on different queries and when I bring them into the main form I cannot total those values.

The values from the subforms are grouped by customer in the main report I need totals. Access doesn't store these values since they are calculated/aggregate fields.

I am creating a massive query with the totals and will be filter data on the report. I don't see any other way.
Thanks again