calcs with null values

[mike02]

Hey

I have a table of returns. It is set up like this:

ProductID..MonthlyDate...Returns...

Now i have a query running to only show the negative returns. It leaves the positive returns Null



IIF(Return<0, Return, "")


Now I then run a query to get basic statistics. Standard Deviation. Variance, and average.

But It will not run. I know it is because there are null values.. I have tried making them zero, or using the NZ() function, but this affects the statitics I want to calculate.

Any ideas how to work around this? Thanks,

[pbaldy]

It leaves the positive returns Null

IIF(Return<0, Return, "")

For clarity's sake, you haven't made them Null, you've made them zero length string's:

http://www.baldyweb.com/NullEmptyEtc.htm

Would it be an option to eliminate positive values from the query via criteria, rather than leaving them in and manipulating the value?

[Kyle02]

out of curiosity, Paul, how do you do this:

If i were to take mike02's information and make a cross tab query, so I cant use the criteria but instead use the IIF, so values would have a zero length string instead of a negative return.

If you were calculating covariance for example, you would need the Sum of XY, for X is one Product and Y is another Product. to get the sum, would result in an error. also, the N value would be different for each product,

Sxy= (1/(n-1))*(EXY-(n*Xbar*Ybar))

im looking at calculating that with this same idea mike02 has posted,

(i am knew, so if i should start a new thread please let me know)

Cheers,

[mike02]

Thanks Pbaldy, worked for the basic statistics!

[pbaldy]

Does using 0 instead of "" work? That should let the sums work.

[pbaldy]

Happy to help Michael!