Calculating percentages in a report, with criteria (1 Viewer)

[D-Angle]

I am trying to calculate a percentage to display in my Access 2010 report but am having some difficulty.

Basically I have 10 questions, to which users can answer with a score of 0 to 3. Users can also choose 'N/A' as an answer if the question is not applicable to them.

I have the scores in text boxes on my report. I initially tried getting the total score by using =Val([1stScore])+Val([2ndScore]) etc., but if any of the text boxes are blank it just hows ###### in the text box doing the calculation. If all the boxes are filled in it will calculate the total just fine, even if N/A is selected in any of the boxes.

I need to be able to sum the scores regardless of if any of the text boxes are blank. I also need to count the number of boxes that don't have N/A as an answer, and use that to express their total score as a percentage.

e.g. if someone scored 3 for 9 questions and N/A for 1 question, they would still get 100%. Does that make sense?

 

[Ranman256]

in the query, the blanks need to be valued via NZ(field)
set query criteria NOT to count the nulls if you want.

then you need a query to count. Count the not nulls, and the total questions.
then you can calc percent.

 

[D-Angle]

I'm hoping to avoid doing it in the query if possible, for various reasons it would be better to calculate it in the report. I can get it to sum and count easy enough, what I'm mostly struggling with is the additional criteria.

 

[D-Angle]

Got it to work within the report! The Dz function helped a lot, thank you. To get the number of questions I managed to count the number of fields that didn't contain N/A, but it didn't include any blank fields, so I counted those separately and added them together.