IIF statement on report column

[psaaccess]

I created a report and user asked me to modify.I have data
always like this or more yes =2 or NA = 2 or both of them 0
1.Totalyesscore =2 TotalNAscore=0
2.TotalYescore=0 TotalNAscore=2
3.TotalYesscore = 0 TotalNAscore = 0

I used IIF statement on report column to add and calculate %. Always calculate 100%

=IIF([tablename.totalyesscore]=0 and [tbalename.totalofnascores]=0,0,[tablename.totalofyesscore]/([tablename.totalyesscore]+[tablename.totalNAscore])

result is 1. 100% (correct result)
2. 0% (wrong) This has to be 100%
3. 0% (correct result)

=IIF([tablename.totalyesscore]=0 and [tbalename.totalofnascores]=0,0,[tablename.totalofyesscore]+[tablename.NAScore]/([tablename.totalyesscore]+[tablename.totalNAscore])
Result is : second resutl is giving 200%

How can I write Nested IIF statement to get correct result?

I appreciate if any one let me know solution ASAP.

Thank you,

 

[MajP]

2.TotalYescore=0 TotalNAscore=2

2. 0% (wrong) This has to be 100%

Can you explain any logic why this should be 100%?

yes/(yes + NA) = 0 / (0 + 2) = 0 AKAIK

What are you trying to calculate? Is it percent of yes answers?

 

[psaaccess]

Yes, It has to be calculate 100% of yes answers and NA answers.

 

[PHV]

Seems you want this:
=IIF(tablename.totalyesscore=0 and tablename.totalofnascores=0,0,1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Are you saying, there are always 2 possible answers, and you are calculating the percent of answered questions?
0,0 = 0
1,1 = 1
1,0 = .5
0,1 = .5
0,2 = 1
2,0 = 1

If that is not what you are saying, please explain what you are calculating.
I do not use nested iifs, I find them sloppy

Public Function percentAnswered(varYes As Variant, varNA As Variant) As Single
  Dim intTotal As Integer
  varYes = Nz(varYes, 0)
  varNA = Nz(varNA, 0)
  intTotal = varYes + varNA
  Select Case intTotal
    Case 2
      percentAnswered = 1
    Case 1
      percentAnswered = 0.5
  End Select
End Function

Place code in a standard module. To use in a control.
= percentAnswered([totalOfYesScore],[totalOfNAScore])

 

[psaaccess]

Hi MJP,

Yes, How can I use that code in the report because total scores are coming from query. I wrote on the report control it won't work.

 

[PHV]

So, why not simply this ?
=(tablename.totalyesscore+tablename.totalofnascore)/2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
=(tablename.totalyesscore+tablename.totalofnascore)/2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[psaaccess]

Hi PHV,

What is /2?

 

[PHV]

What is /2?
Divide by two

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

you can put it directly in the query

select...,percentAnswered([totalYesScore],[totalNAscore]) as PercentAnswered, from SomeTable ...

 

[MajP]

Yes, I actually wrote the function assuming you were doing something more involved and I could modify it. But do what PHV is suggesting if that is all you are asking for.

 

[psaaccess]

Hi majp,

I tried PHV suggestion but it won't work. I am getting required values from table through query. After I got values I am running quesry that do sum of yes, NA scores than I am using IIF function on the report.

So Please tell me, which way I can use solution?

 

[MajP]

Can you provide the SQL for the report, and how you tried to use it in the report? You should be able to use the function in a query or in a calculated control. When you say it does not work what happens? Wrong values, error, error message?