I guess the bottom line is that i just want to have a querry that does addition of fields in way that the AVG command works (in the AVG, if i take... Null, Null, 1, 1 = AVG of 1 rather than .5)

The only reason is I want
"Null" + "Null" = "Null"
rather than
"Null" + "Null" = "0"

but i still want

"Null" + "0" = "0"
and
"0" + "0" = "0"
Thanks a ton

[This message has been edited by kawter (edited 10-22-2001).]

Use Sum and Count, Sum will sum the non null fields. Verify count counts the non-null fields. Then divide Sum by Count.

This is the Field I am trying to create

qrIntBuk4RC: (NZ([tbMstLog4rc],0)+NZ([tbMstLog5rc],0))

Is there a Command such as SUM (with sum I have to make everything an aggregate function... don’t I?),that would only count "NON-NULL" fields, not convert Nulls to 0

I’m so sorry I am confusing all of you with this issue

Thanks again
~Eric


[This message has been edited by kawter (edited 10-22-2001).]

Try this, it uses the IIF to determine how to calculate:

IIf([value2] Is Null,IIf([Value3] Is Null,Null,nz([value2],0)+nz([value3],0)),nz([value2],0)+nz([value3],0))

What this does:

1. Check to see if [Value2] Is Null
2. If it is Null Check to See if [Value3] is Null
3. If both are Null then the Value Returned = Null
4. If both are not null or only one is null then it evaluates the Null values as Zero (0) and adds them together.