% of sums in report totals

[Mike Hughes]

Problem
A report has a Total field in the Report Footer to sum up the columns

There is a column called OPEN CASES and a column called EST TYPE O

In the Report Footer I want the sum’s of each of these columns and I also want the % of the sum of EST TYPE O / OPEN CASES.

The sum of OPEN CASES is a result of =Sum([OPEN CASES])
The sum of EST TYPE O is a result of =Sum([EST TYPE O])

It if create another field in the Report Footer what do I put in the text box to get the percent of EST TYPE O / OPEN CASES ?

Thanks Mike

 

[DCrake]

ControlSource:Nz([EST TYPE O],1) / Nz([OPEN CASES],1)
Format:Percentage

Or

ControlSource:IIF(Nz([EST TYPE O],0) >0 And Nz([OPEN CASES],0)> 0,[EST TYPE O] / [OPEN CASES],0)
Format:Percentage

 

[Mike Hughes]

When I tried
IIF(Nz([EST TYPE O],0) >0 And Nz([OPEN CASES],0)> 0,[EST TYPE O] / [OPEN CASES],0)
I got error:Syntax Error (comma)in query expression 'First((Nz([EST TYPE O],0) >0 And Nz([OPEN CASES],0)> 0,[EST TYPE O] / [OPEN CASES],0)

When I tried

Nz([EST TYPE O],1) / Nz([OPEN CASES],1)

I got error: Syntax Error(missing operator)in query expression First Nz([EST TYPE O],1) / Nz([OPEN CASES],1)

 

[apr pillai]

The sum of OPEN CASES is a result of =Sum([OPEN CASES])
The sum of EST TYPE O is a result of =Sum([EST TYPE O])

Set the Name Property, of the above TextBoxes, with appropriate Names first (say TotalOC and TotalET).

Write the following Expression in the third Text Box, where you want to calculate the percentage:

Control Source: =IIF(nz([TotalOC],0)>0,nz([TotalET],1)/[TotalOC],0)

Format: Percentage

 

[vbaInet]

A percentage requires the divided result to be multiplied by 100. Saying that you want a percentage of a Sum doesn't make sense unless the numerator (i.e.Sum([OPEN CASES])) will never be greater than the divisor (i.e. Sum([EST TYPE])). If this is the case then:

=IIF(Sum([EST TYPE])<> 0, (Sum([OPEN CASES])/Sum([EST TYPE])) * 100, Null) + "%"

 

[Mike Hughes]

Got it, thanks!

 

[vbaInet]

Happy to hear!