Testing fields in a row for shinking

[Steven]

I would like to shrink the fields in one of the bands in a report if all the
amount fields = 0. There are 14 amount fields preceded by a description
field. Therefore if all 14 amount fields are 0 or null then I want to make
them all null and then make the description field null so that row will
shrink. What is the best way to accomplish this?

Thank you for your help,

Steven

 

[Allen Browne]

Use a query as the source for your report.

In the criteria of the query, eliminate the rows where all rows are zero or
null, e.g.:
WHERE (Field1 <> 0) OR (Field2 <> 0) OR (...
There's no need to test for nulls explicitly: that criteria will do it.

BTW, if your table has 14 amount fields, it is probably not normalized. That
looks like a great way to design a spreadsheet (with the columns like a
financial journal), but it is not the right way to design a database table.

 

[Steven]

Allen,

Thank you for your help. I tried to do it in the criteria of the query and
I could not make it work. I had to do it in the Filter of the Report.
Anyway it worked.

You are correct. It is not a normalized. It is for a budget to get Jan -
Dec accross so the users could see each month across on the form. I do not
like to do this and only do I do it in this situation. It does cause other
issues down the road. Is there a way that you can use a normalized table and
for instance where you might have an account that you are budgetting monthly
ie 12 records for that account and then take that and show it accross on one
row in a form and have the user be able to make changes if desired. I do not
know how to do that so in this one case I use the not normalize table but
only in this instance.

Thank you,

Steven

 

[Allen Browne]

Actaully, as a temporary input table to help them figure out what budget
they want, I might use a table like that too. (Presumably there's a process
where the budget is accepted (commmitted) and it then gets written to a
normalized table.)

Not sure why the expression would work in the WhereCondition of a report,
but not in the WHERE clause of the query. But you got it solved anyway.