Main report Subreport adding/subtracting error

[rkrause]

I have a report that have a column and totals up that column, also i have a subreport on this report that has a column and totals up that column.
Then at the top of my report I am adding these to columns up, and subtracting them from a hardcoded number.
My issue is when the subreport doesnt contain any data, the formula does not work.

Is there any way to basically say if theres no data in the subreport its treated like a zero and still works in the formula?

 

[vbaInet]

Try one of the following:

=[COLOR=DarkGreen]SumFromMainRerport[/COLOR] + IIF([[COLOR=Magenta]SubformControlName[/COLOR]].[Report].[HasData], [COLOR=Red]SumFromSubform[/COLOR], 0) - [COLOR=Navy]HardCodedNumber[/COLOR]

=[COLOR=DarkGreen]SumFromMainRerport[/COLOR] + IIF(IsError([COLOR=Red]SumFromSubform[/COLOR]), 0, [COLOR=Red]SumFromSubform[/COLOR]) - [COLOR=Navy]HardCodedNumber[/COLOR]

=[COLOR=DarkGreen]SumFromMainRerport [/COLOR]+ IIF([[COLOR=Magenta]SubformControlName[/COLOR]].[Report].[RecordSetClone].[RecordCount] <> 0, [COLOR=Red]SumFromSubform[/COLOR], 0) - [COLOR=Navy]HardCodedNumber[/COLOR]

 

[rkrause]

here is my formula, so how would i incorporate the formula into mine...
=100000-Sum([act_AnnualizedPayChange])-[SUBrpt_ProductionQuits].[Report].[act_AnnualizedPayChange]

100000 is my hardcoded number

 

[vbaInet]

I have colour coded the names that need to be substituted.

 

[rkrause]

=100000-Sum([act_AnnualizedPayChange])-IIF([SUBrpt_ProductionQuits].[Report].[HasData],[act_AnnualizedPayChange],0)

this is what i have, and im not getting it to come out. i get errors saying invalid synntax.

 

[vbaInet]

I thought you were subtracting the sum from the main report and the sum from the subreport from 100000? If this is the case you need a plus sign (highlighted in blue).

You need the full reference to the subform too:

=100000 - Nz(Sum([act_AnnualizedPayChange]), 0) [COLOR=Navy][B]+[/B][/COLOR] IIF([SUBrpt_ProductionQuits].[Report].[HasData], Nz([Reports]![[COLOR=Red]SubreportControlName[/COLOR]]![[COLOR=Red]ControlNameWithSum[/COLOR]], 0), 0)

By the way, this will only work on a Group Header or the Report Header, not on a Page Header. Amend the what is highlighted in red.

 

[rkrause]

No im starting with a hardcoded value of 100000 then subtracting the total from the main report and then subtracting the total from the subreport. what are the NZ?
my formual below still does not work, any help would be great?

=100000 - Nz(Sum([act_AnnualizedPayChange]), 0) - IIF([SUBrpt_ProductionQuits].[Report].[HasData], Nz([Reports]![
Report.rpt_Maintenance_ControlsQuits]!SUm[act_AnnualizedPayChange], 0), 0)


heres a simple explanation on what im looking for:
starting with 100000
=100000 - 1000(main report total) - 1000(subreport total)
does that help a little better to explain what i want?

 

[vbaInet]

The name of your subreport might be rpt_Maintenance_ControlsQuits but the name of the subreport control may not be rpt_Maintenance_ControlsQuits. Click the subform ONCE, and look in the name property. Clicking it once is the subform control, and clicking it a second time takes you to the report embedded in the control.

SUm[act_AnnualizedPayChange] - you cannot do this in your main report. You must perform the sum in a textbox within the subreport and use the name of that textbox there.

 

[rkrause]

heres my formula now.
=100000 - Nz(Sum([act_AnnualizedPayChange]), 0) + IIF([SUBrpt_ProductionQuits].[Report].[HasData], Nz([Reports]![SUBrpt_ProductionQuits]![textbox53], 0), 0)

i got the report to run but i get #NAME?

 

[vbaInet]

That was my error in syntax

Here:

=100000 - Nz(Sum([act_AnnualizedPayChange]), 0) +  IIF([SUBrpt_ProductionQuits].[Report].[HasData],  Nz([SUBrpt_ProductionQuits]![textbox53], 0), 0)

 

[rkrause]

That worked, thanks.

 

[vbaInet]

You're welcome!

By the way, it's adviceable to give your controls meaningful names. I'm sure you're aware of this but I spotted textbox53.