convert zero to null et al in access report

[Guest]

I want to convert a field, in a record, with zero value to a null value:
opposite of nz?

also when using a calculation such as avg=([x]) the null field should not be
used

 

[John Spencer]

In a query,
FIELD: NoZeroX: IIF([X] = 0, Null,[X])

In a bound control, use the formula above if you haven't done so in the
query. (Make sure the name of the field and the control are different.)

If you don't make the change in the query then in the report use
=Avg(IIF([X]=0,Null,[X]))



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeff Boyce]

Take a look at Access HELP ... I thought I understood that Nulls were not
included in the AVG() function calculation.

By the way, are you sure you want to convert a zero (0), which could mean
"none of it" into a Null, which means ... nothing, don't know, didn't
measure, ...?

If you are sure, use an update query to do this ... BUT! first make a
backup of the database file, in case you decide to revert!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Just one more thing...

Your subject line says "null... in ... report", but that isn't mentioned in
your description.

If you are only interested in the display of the field in a report, DON'T do
the update query, as that will modify the actual data. Instead, use a query
and modify the output using something like:

NewField: IIF([YourField]=0, Null,[YourField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes I want to have the report display null, not zero (missing info) and can
you confirm your question/answer?: are nulls excluded from avg() and other
calculations

as best I'm understand, if using a query, I have to create a new field to
achieve zero to null

or can create it in the report without creating new field?

Take a look at Access HELP ... I thought I understood that Nulls were not
included in the AVG() function calculation.

By the way, are you sure you want to convert a zero (0), which could mean
"none of it" into a Null, which means ... nothing, don't know, didn't
measure, ...?

If you are sure, use an update query to do this ... BUT! first make a
backup of the database file, in case you decide to revert!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to convert a field, in a record, with zero value to a null value:
opposite of nz?

also when using a calculation such as avg=([x]) the null field should not
be
used