How do I suppress zeros in a report

[djones]

I've created a simple tabular report in Access 2003 that lists values for products in 2 columns. The first column has positive values in it and the second has negative values in it. However there are a lot of zero values that just clutter the report and I want to suppress all of them so that they do not show on the report. I've tried modifying the qry that the report is based on to show a null value but then I cannot get a total at the bottom of the report. I've tried changing the hide duplicates property of the fields on the report but this still shows the first zero. Any ideas?

 

[Rich]

<> 0 in the criteria of your query

 

[djones]

Thanks Rich,

In this instance it doesn't work as I am derriving the 0 value based on the data contained within another field with the formula iif([field1]) = "A", [Field1], 0). My first thoughts were to change the 0 to "" or Null. This works but when I try to total the report it gives me an error. If I put the <>0 in the query criteria for both columns I don't return any records.

 

[ptaylor-west]

Did you ever get this sorted as I am having the same problem

 

[ptaylor-west]

Found the solution on another site

Set the Format Property of the control to:

#;-#;""

 

[EdK]

That's brilliant and it works to show blank space instead of the annoying zero. However it doesn't hide the corresponding text field entry (see attached budget report). So, what is the expression to hide the receipt type label or the payment type label when its budget figure totals zero? And how/where do I put the expression, please? Thanks in advance By the way, my knowledge and use of VBA is very limited (sorry) EdK

 

[theDBguy]

Hi @EdK. You might consider starting a new thread instead of adding on to an old one (15 yrs old).

In any case, did you try adding the <> 0 criteria as suggested in Post #2?

 

[EdK]

Hi DB Guy
actually, I got around the problem the clumsy way, which was to totally delete the transaction. This is OK for my purposes, although it doesn't leave an "audit trail" in the data base, but I keep a printed out record of what the budget report info was before then .... NFA & thanks for your interest - if it becomes a problem in the future I will put it up as a new post .... EdK

 

[theDBguy]

Hi DB Guy
actually, I got around the problem the clumsy way, which was to totally delete the transaction. This is OK for my purposes, although it doesn't leave an "audit trail" in the data base, but I keep a printed out record of what the budget report info was before then .... NFA & thanks for your interest - if it becomes a problem in the future I will put it up as a new post .... EdK

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

Deleting the transaction is the wrong solution. Would you cut off your nose if you had a cold?

Criteria in a query is how we control which rows are included and which are excluded. Therefore, rather than binding the report to a table, you would create a query that had criteria and use that as the RecordSource for the report.

Select ...
From ...
Where IsNull(SomeField,0) > 0;

This handles both null and zero. It also assumes that you don't have negative values. If you just want to exclude 0 and null then

Where IsNull(SomeField,0) <> 0;

 

[jdraw]

I think Pat meant to write

Where NZ(someField,0)<>0

 

[Pat Hartman]

Sleep typing. Thanks