skip null value in field when calculating

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
 
A

Annemarie

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
 
K

KARL DEWEY

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...
 
A

Annemarie

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding the value of two fields when value is null 4
Counting in Query 3
counting Null fields 3
Calculating Avg with null values 14
Counting records in a table 5
Counting fields in a table row 1
Update query to remove data 3
A followup to my TALLY NULL question 2

Top