hiding blank (null) and/or zero value fields on a report

[Karen]

Can blank (null) and/or zero value fields be hidden on an Access report? I
have a report that has 15 test fields. Not all of the products have 15
tests. The reports for those products show blank lines for the fields with
no data in them. I would like to keep those line from 'printing' so the
report will be on one page for that report and it would make it look nicer
too.

Thanks,
Karen

 

[Joseph Meehan]

Can blank (null) and/or zero value fields be hidden on an Access
report? I have a report that has 15 test fields. Not all of the
products have 15 tests. The reports for those products show blank
lines for the fields with no data in them. I would like to keep
those line from 'printing' so the report will be on one page for that
report and it would make it look nicer too.

Thanks,
Karen

This sounds like a bad database table design.

Normally you would have a produce table and that would be linked to a
test table. Each test would be a single record in the test table.

Using this setup, your report would normally default to what you want as
there would be no blank test data.

Note: this is just one of many reasons standard normalization of tables
is important.

I suspect you now have one record for each product with 15 fields
devoted to tests.

 

[Karen]

Yes. My table is set up that way. I don't know how I would do some of the
other things I'm doing with these test fields if they are in a test table.
On a form in the app I have the Quality Control dept entering their test
results. The test results are compared to the high and low values for that
test and flagged if the result isn't compliant. Would I still be able to do
that on a form if the tests and specs were in their own table?

The QC form has the product number and some other information then a list of
the tests each with its low and high value and a field for the qc test
result.

 

[Guest]

Karen: Joseph is right about the design, but there is a kludgy way to
display what you want (without sending the test results out to temp tables to
run the report from). I assume that the test results are stacked vertically
on the report. If you can assure that all controls on each line of the
results produces a null if it is not wanted (that is, the test value is null
or zero), try this:

- Set each field in the stack to Can Grow.
- Reduce the vertical size of the test result control to zero (drag the
bottom to the top so it looks like a line)
- Relocate the 2nd control upward over the first, then the 3rd over the
second, etc. (there will appear to be only one line when you are done).
- Reduce the vertical height of the detail section to just below the
"stacked lines" (the reduced controls stacked up).

You'll probably have to work out some other detail formatting, but try a
test of this on a new report. Remember, the whole line referring to a test
will have to be null if it is to be a "no-show" and all controls on each line
vertically reduced this is to work.

This is really a kludgy way to do this, but I have had to resort to this
method when designing reports well after an application has been in operation
and redesign was impractical or the client was unwilling to pay for a
redesign/rebuild.

HTH Joe

 

[Joseph Meehan]

Yes. My table is set up that way. I don't know how I would do some
of the other things I'm doing with these test fields if they are in a
test table. On a form in the app I have the Quality Control dept
entering their test results. The test results are compared to the
high and low values for that test and flagged if the result isn't
compliant. Would I still be able to do that on a form if the tests
and specs were in their own table?

I would think it might even be easier. I would need to better
understand the whole process before saying for sure.