Calculate Averages W/O using the DAvg Func in a Query?

[Cleis]

Hi All:

I want to be able to calculate the average for a field. In this field you can have 0,1,2,3,4 and 5. 0 represents &quot;Does not apply&quot;. So I don't want to have any zeroes calculated into my average. Becasue of this, I cannot use the Avg() Func. I could use =DAvg([QR1],&quot;qryResponse&quot;,[QR1]<>0). However, I have ten fields that I want to do this calc on and it's very slow. Further, the table in which I want to do this calc on is going to be up on a network . . . .thus it will be much slower.

How can I overcome this speed issue?


Thank you!


Rich

 

[Junior1544]

open a query, and click on view then on totals...

there is a funtion in there that will calculate the average... i don't know about not including 0's though...

Another way is to do it all in vba... I tend to do things in vba any way myself... but i also enjoy the programming...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Cleis]

Junior1544:

Thanks for your response. What I want to do will not work by a query all alone. I'm going to have to build a func and call it from the query . . . any ideas on syntax or where to start???

Regards!


Rich

 

[Junior1544]

this is just off the top of my head and untested... but it's an idea...

--James



Function test(numb as integer) as integer

if numb = 0 then
test = &quot;&quot;
else
test = numb
end if

end function
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Cleis]

Too close to the tree to see the forest! I've been sitting behing this computer way too long.

Found this on the MS Site:

Create a module and paste this:

Function ZeroToNull( MyValue As Variant) As Variant

If MyValue = 0 Or MyValue = Null Then

ZeroToNull = Null

Else

ZeroToNull = MyValue

End If

End Function


Works like a charm. Sometimes things are so darn simple they are overlooked!

Regards,

Rich

 

[Junior1544]

that's almost the same as what i did for you off the top of my head...

(I guess it's not bad that I have family that works for micorosoft)

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.