IIF Statement not working...

[LanieB]

I am creating a database to store audit scores
There is a section of Bonus Points. So obviously the points AVAILABLE will always be 0. When showing these results on a report I want to see (on average) how many people actually score bonus points. This is the statement I am using...



DO_Green_AA = Points Available
DO_Green_AE = Points Earned

*Thinking Green*: IIf([DO_Green_AA]=0,2,[DO_Green_AE]/[DO_Green_AA])

I thought this would be telling the equation to change any 0 in the available column to a 2... did I not write it correctly?

[LanieB]

Is the statement I have written up there saying If DO_Green_AA is 0, change it to a 2...?

[orange]

No.

*Thinking Green*: IIf([DO_Green_AA]=0,2,[DO_Green_AE]/[DO_Green_AA])

*Thinking Green*: represents a field name (bad use of *)

This is saying,
if [DO_Green_AA]=0 then *Thinking Green* will be valued 2
otherwise *Thinking Green* will be valued [DO_Green_AE]/[DO_Green_AA]

[June7]

I am confused. If Points Available will always be 0 why are you dividing by Points Available?

[DO_Green_AE] / IIf([DO_Green_AA]=0,2,[DO_Green_AA])

Should avoid special characters (* @ # $ % & / + - < >), punctuation (underscore is exception), spaces in names or reserved words as full names. If used must enclose in [].

[LanieB]

They want to be able to see on the report the average of people that actually got bonus points.

How would I write the equation to change a 0 to a 2? I see now what you are saying with my previous IIF statement.

[June7]

I gave you the expression. Is it working as you expect?

Dividing points earned by points available will give average of people that got bonus points? Not really following that but if it is what you want...

[LanieB]

I got this figure out! Thank you so much for all of your help!~