Calculating Average between two number on Report (1 Viewer)

[rxm01l]

Need advise. I have two fields in my Access report that I want to find the Average. Field1 is "Pick%" and Fields2 "HL%". How should I do to find Average between these two fields? seem simple but I just can't get right.

In Control Source: I have the following.
=Avg ([Pick%]+[HL%]).. Not Correct..

Help.. please..

 

[plog]

= ([Pick%]+[HL%])/2

 

[rxm01l]

Yeah, Divide by 2 would work if either field is Not Null, when one field is blank, I got wrong answer.. I'm looking for Avg function that would make my report dynamic.. Of if I have more than two fields.. etc..

 

[plog]

1. You should test for nulls using the NZ() function.

2. "Dynamic"? I mean if you have to manually tell it what fields to use, its never going to be that dynamic. If you are telling it what fields to add and might add more you are already editing the thing, just change the denominator while you are there.

3. Roll your own. You could create a function, pass it an array of what you want to average and then do all the logic there (check for nulls, count entities, sum, average).

4. With your field names as they are and you wanting a "dynamic" query, I fear you don't have your query nor tables set up properly. I mean, in SQL there is an AVG() function you can use and it does what you want. But you have to have a properly structured table/query to do so.

 

[rxm01l]

Thanks You.. I may have to restructure my table .. I thought there may be an easier way to just put Avg function on the report and let it rip.. Thank You sir..