Calculation close and Yet Not

[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 number or negative number without effecting for example Auto Breaking which is correct so I don't want to multiply the correct fields. I figure is 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%

 

[tlbroadbent]

Neither 100% nor 200% is the correct percent of change in the example you've provided. The change (2) divided by the previous value (0) is undefined. Of course, you realize that and used the IIf function to avoid the problem of division by zero. You can easily produce the result you want with the modification I suggest below. Consider, however, that the result is mathematically incorrect. If it meets your needs, then by all means use it.

Percent:IIf([Total Of Crime Types1]>0,
Format([Change]/[Total Of Crime Types1],"Percent&quot,
Format([Change]/1,"Percent&quot) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tlbroadbent]

Actually, the formula is simpler than what I posted. The last format statement can be...

Format([Change],"Percent&quot

There's not much difference but there's no need to complicate things. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[KayJen]

Works Greats, Thanks for the reply...