Average Fields and Null Values

[hotrodsue]

This forum has helped me on so many occassions. Over the last 2 days I have searched and found answers so close to what I need.

My issue: I have a query in which I average 3 fields. In some cases all three fields may be null. When they are I get an #Error in my average field.

In several posts I've read the following:

You need to determine which is the correct answer in your situation:

3, 0, 3 = 2 OR 3, null, 3 = 3

If you want null values considered to be zero for the purpose of the average, you'll be ok with this solution. If not, you'll need a more complex calculation that counts the number of non-null values to use as the divisor OR better still - normalize your schema.

I'm looking for ideas on how to avoid the #Error when all three fields being calculated are Null. Any suggestions will be so appreciated. Thanks!

 

[Alansidman]

I think that you need to look at the NZ function.

http://www.techonthenet.com/access/functions/advanced/nz.php

this link will explain its usage.
Alan

 

[jjturner]

Hi hotrodsue,

I agree with the last part of the quote you refer to: you should seriously consider restructuring - i.e., "normalizing" - your table(s).

The problem you're attempting to overcome is indicative that your data structure is flawed. The use of the repeating groups (3 numeric fields of the same type) is the most conspicuous example of that. If you continue with what you have, you're almost sure to encounter other similar problems when it comes time to get results from your queries.

You may want to post your table structure in the Tables forum to get some feedback as to how best to restructure.

Regards,
John

 

[hotrodsue]

Thanks so much for your post and answers to my questions. I double checked my code AND found my error. It's working wonderfully!