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 20022005, 20072010 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 