Summing fields, ignoring certain values

[ahuvas]

I have a number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.

Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2
I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102


I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?

 

[Uncle Gizmo]

I believe the "immediate if (IIf)" is applicable here.

Q1 + Q2 + Q3 + IIf(Q4 = 99, 0, Q4)

reading that in English:
If Q4 = 99, then return zero, otherwise Whatever is in Q4.

 

[capsula4]

What you are looking for may look like this probably:

IIf(Q1 = 99, 0, Q1) + IIf(Q2 = 99, 0, Q2) + IIf(Q3 = 99, 0, Q3) + IIf(Q4 = 99, 0, Q4) + ... and so on if you have more fields

 

[ahuvas]

Thank you everyon! I will try this,