Prev: Label Report
Next: Memo in Report causing blank pages/other issues
From: DAShadow on 26 May 2010 01:03
Hope someone can help me solve this...I really need some help, I have tried
and read alot and can not find the answer I am looking for.

I am trying to get this expression to work in my database.
I have 3 fields QC%, InBound% and 3rd Party% that have percentages.
using a bound field:
( I have used IIF and NZ to assist with Null values)
QC%=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed
Audits],'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100

InBound%=IIf(nz([SumOfInBound Total],'0')=0,0,Sum(nz([SumOfInBound Passed
Audits],'0'))/Sum(nz([SumOfInBound Total],'0'))*100

3rd Party%IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd
Party Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100

I am trying to get these to add together by using a unbound field:

=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed Audits]
,'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100\1+IIf(nz([SumOfInBound
Total],'0')=0,0,Sum(nz([SumOfInBound Passed Audits],'0'))/Sum(nz(
[SumOfInBound Total],'0'))*100/1
+IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd Party
Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100\1

I added \1 into my formula to move to each of the next fields (QC%, InBound%
and 3rd Party% to add it properly...which actually works BUT now I need to
divide by each field. I need to find out how to ask it to move to each field
if there is a 0 or a value so that I can divide it to get a Overall total.
If I have 3 values this works fine but when I only have 1 value it is
dividing by 3 when actually there is only 1 value...the rest are 0's.

I am adding these 3 fields together to get one number that is a Overall
Percentage for each field (QC%,InBound% and 3rd Party%).

Is there something I add to this formula...a wildcard?? Please help!

Thanks,
DASH

From: John Spencer on 26 May 2010 09:15
At least part of your problem is that you are returning text values instead of
number values in your expressions. The quotes around the zeros turn them into
text values and if the IIF statement has a requirement to return a string for
any of its arguments it will convert all the return values as strings.

QC%=IIf(nz([SumOfQC Total Number Audited],0)=0,0,Sum(nz([SumOfQC Passed
Audits],0))/Sum(nz([SumOfQC Total Number Audited],0))*100

InBound%=IIf(nz([SumOfInBound Total],0)=0,0,Sum(nz([SumOfInBound Passed
Audits],0))/Sum(nz([SumOfInBound Total],0))*100

3rd Party%=IIf(Sum(nz([SumOf3rd Party No Audits],0))=0,0,Sum(nz([SumOf3rd
Party Passed Audits],0))/Sum(nz([SumOf3rd Party No Audits],0))*100

Adding the percentages together and dividing by 3 to get an overall percentage
seems a bit suspicious to me as a reliable measure unless all three items were
of equal importance and of equal weight.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

DAShadow wrote:
> Hope someone can help me solve this...I really need some help, I have tried
> and read alot and can not find the answer I am looking for.
>
> I am trying to get this expression to work in my database.
> I have 3 fields QC%, InBound% and 3rd Party% that have percentages.
> using a bound field:
> ( I have used IIF and NZ to assist with Null values)
> QC%=IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed
> Audits],'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100
>
> InBound%=IIf(nz([SumOfInBound Total],'0')=0,0,Sum(nz([SumOfInBound Passed
> Audits],'0'))/Sum(nz([SumOfInBound Total],'0'))*100
>
> 3rd Party%IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd
> Party Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100
>
> I am trying to get these to add together by using a unbound field:
>
> =IIf(nz([SumOfQC Total Number Audited],'0')=0,0,Sum(nz([SumOfQC Passed Audits]
> ,'0'))/Sum(nz([SumOfQC Total Number Audited],'0'))*100\1+IIf(nz([SumOfInBound
> Total],'0')=0,0,Sum(nz([SumOfInBound Passed Audits],'0'))/Sum(nz(
> [SumOfInBound Total],'0'))*100/1
> +IIf(Sum(nz([SumOf3rd Party No Audits],'0'))=0,0,Sum(nz([SumOf3rd Party
> Passed Audits],'0'))/Sum(nz([SumOf3rd Party No Audits],'0'))*100\1
>
> I added \1 into my formula to move to each of the next fields (QC%, InBound%
> and 3rd Party% to add it properly...which actually works BUT now I need to
> divide by each field. I need to find out how to ask it to move to each field
> if there is a 0 or a value so that I can divide it to get a Overall total.
> If I have 3 values this works fine but when I only have 1 value it is
> dividing by 3 when actually there is only 1 value...the rest are 0's.
>
> I am adding these 3 fields together to get one number that is a Overall
> Percentage for each field (QC%,InBound% and 3rd Party%).
>
> Is there something I add to this formula...a wildcard?? Please help!
>
> Thanks,
> DASH
>
 | 
Pages: 1
Prev: Label Report
Next: Memo in Report causing blank pages/other issues