What's the opposit of nz

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a numeric field. Some of the fields contain zero values.
For the purpose of calculating an average I want to ignore these fields.
How do I make them null.
 
Why do you need to change the Field value to null, just write a criteria
Where Value <> 0.
 
Beacuse I am trying to run a summary query, using group by and an average
over 18 data fields. Using "<>0" as a criteria won't work. All the records
contain some zero values, and all the fields contain some zero values but
there is no consistency in what cells are actually zero,. Using "Average"
will ignore nulls when calculating, but a value=0 skews the average.
 
In your query, if "F1" is your numeric field, enter the following formula in
the "Field" grid: IIf([F1]=0,Null,[F1])

Then specify Avg as your aggregate function on the "Total'. Example--Data
table:

F1
---
6
0
7
0
12

Query showing average WITH zeros and WITHOUT Zeros:

Field: F1 NullF1: IIf([F1]=0,Null,[F1])
Table: Table
Total: Avg Avg

Produces Results:
F1 NullF1
---- ------
5 8.333

/LJ
 
I'm not familiar with such a function, but you can use the iif statement
iif([Fieldname]=0,Null,[Fieldname])
 
Thank you Offer and LJones. Funnily enough that was my first thought, but
when I tried it initially I was getting a "Data Type Mismatch" error.
However, having gone back and tried again it now works perfectly.

Thanks again

LJones said:
In your query, if "F1" is your numeric field, enter the following formula in
the "Field" grid: IIf([F1]=0,Null,[F1])

Then specify Avg as your aggregate function on the "Total'. Example--Data
table:

F1
---
6
0
7
0
12

Query showing average WITH zeros and WITHOUT Zeros:

Field: F1 NullF1: IIf([F1]=0,Null,[F1])
Table: Table
Total: Avg Avg

Produces Results:
F1 NullF1
---- ------
5 8.333

/LJ
Alan Mitchell said:
I have a table with a numeric field. Some of the fields contain zero values.
For the purpose of calculating an average I want to ignore these fields.
How do I make them null.
Click to expand...
Click to expand...
 
Back
Top