Need Help with a Statement in my report

[rohanmalhotra83]

Hi all

I am trying to use IF/ELSE statement in one of the reports I am building, however, not able to do it.

Basically, I want the statement to look at the Average of Score and then return a value accordingly. The breakdown is below :

Score is 30 - Platinum
Is between 25 & 29 - Gold
Is between 20 & 24 - Silver
Less than 20 - Bronze

The statement i have written is below :

=IIf(Avg([Total]=30),"Platinum",IIf(Avg([Total]<30) And (Avg([Total]>24)),"Gold",IIf(Avg([Total]<25) And (Avg([Total]>19)),"Silver","Bronze")))

Thank you in advance.

 

[plog]

First, this: (Avg([Total]=30) should probably be this: (Avg([Total])=30). What you have probably won't resolve to anything and might throw an error.

Second, when you get that deep into IIf's, its time to write a custom function in a Module:

Function getStatus(A)
    ' takes average scores, A, and returns status based on that score
 
ret = "Bronze"
    ' default value
 
if (A>=20) ret="Silver"
if (A>=25) ret="Gold"
if (A>=30) ret="Platinum"
 
getStatus=ret
 
End Function

After putting the above code in a module, to use it, you replace your humongous iff statement with this:

=getStatus(Avg([Total]))

 

[rohanmalhotra83]

First, this: (Avg([Total]=30) should probably be this: (Avg([Total])=30). What you have probably won't resolve to anything and might throw an error.

Second, when you get that deep into IIf's, its time to write a custom function in a Module:

Function getStatus(A)
    ' takes average scores, A, and returns status based on that score
 
ret = "Bronze"
    ' default value
 
if (A>=20) ret="Silver"
if (A>=25) ret="Gold"
if (A>=30) ret="Platinum"
 
getStatus=ret
 
End Function

After putting the above code in a module, to use it, you replace your humongous iff statement with this:

=getStatus(Avg([Total]))

Thank you for the solution. However, how will I call this function in report?

 

[rohanmalhotra83]

Thank you for the solution. However, how will I call this function in report?

It worked... Thanks a ton