Calculation close and Yet Not 1

[KayJen]

I have a query that groups and counts the crimes by the parameters have previously set in another query. When the field is empty, (good thing no crime) I used the following IIf statement to give me 100%: Percent:IIf([Total Of Crime Types1]>0,Format([Change]/[Total Of Crime Types1],"Percent&quot,"100.00%&quot, however, if you notice with purse snatching there is a total increase of 200% not 100%. What I am looking for is a way to calculate "Change" by 100% be it a positive or negative number, without effecting for example, Auto Breaking, which is correct, so, I don't want to multiply the correct fields. I figure it needs to say something like, If TotalWk1 is blank and TotalWk2 is not then multiply "Change by Percent" and vise-versa with TotalWk2. Any advise would be greatly appreciated. Thanks, Sonny


Type of Crime TotalWk1 TotalWk2 Change Percent:

Purse Snatching 0 2 2 100%
Auto Breaking 15 13 -2 -13.33%

 

[jebry]

Hi Sonny!

Try this:

Percent:IIf([Total Of Crime Types1]>0,Format([Change]/[Total Of Crime Types1],"Percent&quot,Format([Change],"Percent&quot

I think this will get you what you are looking for.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[KayJen]

Your reply was not only quick but to the point. Two days of headaches are gone. A BIG Thanks...
Sonny