Hiding and Moving fields on a report

[ions]

Dear Access Expert,

1) I am creating an invoice report that has the following summary fields.

Subtotal
Discount
Environmental Surcharge
Tax
Deposit
Final Total

If there is no Discount the field should be hidden from the invoice which is easy to do but then there is an empty gap that looks unprofessional. Deposit should also be hidden if it's 0.

How would you go about accomplishing this? Using VBA or creating separate reports? My preference is VBA but I would like to hear other people's experiences. There are four variations of the above.

Discount Y Deposit Y
Discount Y Deposit N
Discount N Deposit Y
Discount N Deposit N

Thank you

 

[TexanInParis]

If you allow both Deposit and Discount to be Null when they don't exist, then it's easy to accomplish what you want. Simply make the two text boxes (and attached labels) 0" in height and placed so they don't overlap anything else. Set Can Grow to Yes, and they will expand to print something when not Null.

 

[ions]

Thank you for your response John.

Associated labels don't grow/shrink in Access 2007 or less but I did accomplish what I wanted with your suggestion.

I made the label into a textbox and kept the label and the deposit field the regular height. When the value was null I used Can Shrink on both. I made the label Control Source= IIF(IsNull([txtDeposit]),[txtDeposit],"Deposit")

For this to work ensure that no other fields are in the same horizontal plane as the fields that shrink.