Remove zero filed from report

[stevehard123]

Name: Access.jpg  Views: 11  Size: 13.9 KB  ID: 47608" class="thumbnail" style="float:CONFIG" />I've created a DB which is running fine, much to my newbie surprise, but I'm having a problem with the report.

As in the example above, if a field is a zero value then I don't want it to be displayed and everything below to move up by one. There may be occasions where 2 or more field are zero also.

I think I've ran out of Google search terms to find a solution to this problem and I'm hoping some of your wonderful people here have a simpletons fix for me as I'm learning as I go.

Thanks in advance.

[mike60smart]

Hi

I am assuming that the Report is based on a query?

In the criteria row for the Numeric Value field use <>0

[stevehard123]

Thanks for the reply and Yes it is based on a query.

Unfortunately, when I do this, it reverts to a record where the field is greater than 0 and bypasses the visible record altogether.

[mike60smart]

Hi
Can you upload a copy of the database?

[stevehard123]

Unfortunately no, it has too much sensitive info on it. I know it would be the best thing to do but I'm afraid that's not an option.

I created it to just hold clients info but then thought I'd be clever and push it to the next level, so I'm not fussed if I can't sort this as it was never part the original plan, just frustrating that this one little thing is preventing me from moving it forward further than expected. I can get the desired result in Excel and probably word, I just thought it would make life easier by using just the one piece of software.

[Micron]

Unfortunately, when I do this, it reverts to a record where the field is greater than 0 and bypasses the visible record altogether.

I don't understand that. Seems like that's what you wanted to happen. What's "the visible record"? When there's zeros, they're all visible are they not?

A posting option is to mock up some data in Excel, center the fields and post it here as a sample. You'll get a pretty decent table and we won't have to make up data. Second table made same as the first but showing the desired outcome.

[Gicu]

I have a feeling that your table is not properly designed. Can you show us the table with the fields you show us in your original post? If Income, Capital Allowance, Motor Vehicle are individual fields in the table then you have a problem. You need to read about database normalization, but basically you need two fields (ExpenditureType and ExpenditureAmount) so you split your existent record in multiple records. Then reporting would be easy as you just group by customer (id) and probably some date or other field(s), sort your expenditures and eliminate the zero ones in the criteria.

Cheers,