skip null value in field when calculating

  • Thread starter Thread starter Annemarie
  • Start date Start date
A

Annemarie

I'm trying to calculate the score from survey questions, but don't get an
accurate total if there are null scores. I'm working with over 40,000 surveys
so filling in the null fields to something isn't possible. Is there a formula
I can add to my query that skips the null fields and totals the score(s)
without it?

Thanks
 
This is what I have:
Expr1:
Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5)

and it doesn't work.

comments_tbl is the name of my table the query is pulling the data from
Question_1 - Question_5 is the name of the fields in the table
 
Try this --
(Nz([comments_tbl].[Question_1],0)+Nz([comments_tbl].[Question_2],0)+Nz([comments_tbl].[Question_3],0)+Nz([comments_tbl]![Question_4],0)+Nz([comments_tbl].[Question_5],0))/5

--
KARL DEWEY
Build a little - Test a little


Annemarie said:
This is what I have:
Expr1:
Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5)

and it doesn't work.

comments_tbl is the name of my table the query is pulling the data from
Question_1 - Question_5 is the name of the fields in the table





KARL DEWEY said:
Check out the Nz function.
Click to expand...
Click to expand...
 
that worked, thank you!

KARL DEWEY said:
Try this --
(Nz([comments_tbl].[Question_1],0)+Nz([comments_tbl].[Question_2],0)+Nz([comments_tbl].[Question_3],0)+Nz([comments_tbl]![Question_4],0)+Nz([comments_tbl].[Question_5],0))/5

--
KARL DEWEY
Build a little - Test a little


Annemarie said:
This is what I have:
Expr1:
Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5)

and it doesn't work.

comments_tbl is the name of my table the query is pulling the data from
Question_1 - Question_5 is the name of the fields in the table





KARL DEWEY said:
Check out the Nz function.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to calculate the score from survey questions, but don't get an
accurate total if there are null scores. I'm working with over 40,000 surveys
so filling in the null fields to something isn't possible. Is there a formula
I can add to my query that skips the null fields and totals the score(s)
without it?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top