Exclude zero fields in reports

[tonsam]

I have a report (monthly inventory) whose record source is based on a query named monthly_invento ry_qry. On same query (monthly_invent ory_qry), I add two fields (month_1 and month_2)and its sum was contained in the field "month_tota l". I tried to exclude all the zeros on field "month_tota l" by placing ">0 " on the "criteria" part of the query builder and it works on the query when I view it in datasheet view. However, when i run the same query on the report, zeros from field "month_tota l" are still included. How do you think I can exclude all the zeros on "month_tota l" when I run my report? Your assistance is very much appreciated!!! Thank you in advance!

[puppydogbuddy]

I have a report (monthly inventory) whose record source is based on a query named monthly_invento ry_qry. On same query (monthly_invent ory_qry), I add two fields (month_1 and month_2)and its sum was contained in the field "month_tota l". I tried to exclude all the zeros on field "month_tota l" by placing ">0 " on the "criteria" part of the query builder and it works on the query when I view it in datasheet view. However, when i run the same query on the report, zeros from field "month_tota l" are still included. How do you think I can exclude all the zeros on "month_tota l" when I run my report? Your assistance is very much appreciated!!! Thank you in advance!

Try placing this in the criteria row of the field month_total to exclude 0's and nulls:
nz([month_total],0) <> 0

[tonsam]

I have tried replacing "<0" by "<>0" on the criteria row but still the report displayed zeros.

[damonreid]

Have you tried adding the criteria "Is Not Null" as well on the query?

[tonsam]

Thanks for your reply but I tried it as well but still it did'nt work. Zeros continued to display on the reports eventhough in the query there are already no zeros when I open it in datasheet view.

[damonreid]

This is a silly question but I have done it before, you are pointing the report at the query and not the table?

[tonsam]

This is a silly question but I have done it before, you are pointing the report at the query and not the table?

Yes. I tried to use it on the query which is the source of the report. That's what really giving me headache, the report must not display zeros on the "month_tota l" field because on the query there are no zeros already. Do you think I have to use codes on the report? Thanks!

[puppydogbuddy]

Try placing this in the criteria row of the field month_total to exclude 0's and nulls:
nz([month_total],0) <> 0

If you used the expression I suggested above in your query, and use the query as the record source for your report, it should work unless:
1. you have some conflicting code behind your report. Post all the relevant code behind your report.
2.You have bound the textbox controls on your report to some conflicting expressions instead of binding them to the query.

[NeoPa]

How have you applied the filtering to your query?
I suggest that you post the SQL of your query in here for us to look at.