How do I hide some fields of a record and shrink the detail band of a report?

[jbt007]

Hi all,

I am developing an access report where the fields contain multiple "categories " of cost. A sample is attached.

There are 6 different categories: Quantity, Labor, Hours, Material, Subcontract, & Supplies.

There are several types of cost for each category: Budget, Actual, Earned, Variance, Unit Rate, Wage Rate, etc.

What the client would like is a 8 1/2 x 11 Landscape report showing the cost code's cost in the following format:

Activity, Title
Quantity (quantitiy data in this row)
Equipment (equipment cost types in this row)
Supply (Supply cost types in this row)
etc, etc. (see attached sample)

Because all cost pertaining to each cost code is one record, I am at a loss on how to put this in a report that doesn't show a bunch of "blank space" because not all records have all categories of cost. The detail band needs to "shrink" down to show only categories that have some type of cost in at least one cost type. So the fields would have to "float" in the detail band up to the top of the band if the category above it had no cost. (If that makes any sense.) I have attached a sample from their legacy system.

I started building a Union query to get a unique record per cost code per type of cost, then I would be able to generate a query that's "stacked" by cost category and any blank categories would not be in the record set, but before I went through all that work, I was hopping there was an easier way. I would be greatful for any feedback.

Thanks,

[jbt007]

You have to set the Band properties to "Can Shrink" AND every field's txt box to "Can Shrink". This will hid any field that's null. In the query, make sure to set all fields to null that are zero with "IIF([fldName]=0,Null,[fldName])" function.