Hiding Zero Values

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I have a report that I would like to hide zero values. The query that it is
linked to calculates the sum of different fields and is using the nz
function to calculate null values.
Is there a way to hide zero values?
Thanks
David
 
DavidW said:
I have a report that I would like to hide zero values. The query that it is
linked to calculates the sum of different fields and is using the nz
function to calculate null values.
Is there a way to hide zero values?
Click to expand...


Use a custom format in the text boxes where you want to hide
a zero value. For example, if your values are one place
decimals, the format might be
0.0;;"";""
 
DavidW said:
That did hid the zero, is there a way to hide the whole record that has a
zero value?
Click to expand...

I don't understand, if you don't want a record in a report,
shouldn't it be filtered out before the report ever sees it?
--
Marsh
MVP [MS Access]


 
Learned Something Today
How do you get zero values out of a query?
I am using calculated fields to sum a group of fields with the following

=sum(nz(firstfield,0))+sum(nz(secondfield,0))

there are some null records in different fields, is there a different way to
handle nulls that I havent learned about yet?
thanks for responding back
 
DavidW said:
Learned Something Today
How do you get zero values out of a query?
Click to expand...

Just set the field's Criteria to <>0 to prevent the report
from seeing records where that field has a value of 0 or
Null (since Null compared to anything is never True). You
can filter out just the records where a field is Null by
using a criteria of Is Not Null

In general, the criteria used in the Access query design
grid is restructured into an SQL statemet's Where clause
(which is an extremely powerful and necessary capability in
dealing with any SQL compatible database system).

I am using calculated fields to sum a group of fields with the following

=sum(nz(firstfield,0))+sum(nz(secondfield,0))
Click to expand...

That's redundent, but harmless, as long as you want the
report to display the records where firstfield or
secondfield is Null. Note that the aggregate functions
(Count, Sum, Avg, etc) all ignore Nulls, so the Sum you're
using would have the same result as just using
=Sum(firstfield)+Sum(secondfield)

there are some null records in different fields, is there a different way to
handle nulls that I havent learned about yet?
Click to expand...

See above.
 
Back
Top