Hide Blank Columns in Report

[jneirinckx]

Good afternoon,

This is a continuation of my last post about hiding blank columns in my report at run time. I have been able to set the visible property to false but the can shrink does not eliminate any blank space. I have attach a pic of my report to show you what I am trying to achieve.

Thanks for any advice you can give.

Jerry

 

[vbaInet]

Can Shrink works on a row-wise basis, not on colum-by-column basis. You've shown us a screenshot but you haven't indicated or explained which columns you're trying to hide.

 

[jneirinckx]

Thank you for your quick reply.

Would like to hide any column that does not contain data ie. KO2, KO3, KO6 etc

Thanks

 

[vbaInet]

Hide them and leave the space between KO1 and KO5?

 

[jneirinckx]

If possible remove the space between them.

 

[vbaInet]

It's not as straightforward as you would think it is because the entire thing needs to be coded. Where is this coming from, a crosstab query?

Create a query that will give the Sum of each of those fields and ensure that where there's no data it returns 0 at least.

 

[jneirinckx]

It is just coming from a select query which is attached to a table that the user enters the numbers and date. All calculations are done in the query ie: totals, percent etc. I can modify the calculation to return zero if that is important.

Thanks

 

[vbaInet]

If that is just a table then your table is not normalised. What would you do if you have a KO17 was needed? You would go and created a new field?

Is it a query that's displaying the totals at the bottom? Upload a cut down version of your db with some sample data.

 

[jneirinckx]

I will do that but have to go out for a while now so look for it tomorrow.

Thanks again

 

[vbaInet]

No problem. Myself or Bob will pick it up tomorrow.

 

[vbaInet]

Well, since I had a bit of time and there were no new threads I thought I should knock up a quick example. See attached and view the report rptRecords.

 

[jneirinckx]

Good Morning,

That's it vbaInet but how did you do that. I studied your report but can't find any code. I have attached a Sample DB of what I am working on so if you could give me some direction on how you accomplished that, that would be great.

Thanks again
Jerry

 

[vbaInet]

The code is in the Format event of the report's Header section. Have a look there and also pay close attention to the naming convention of the controls (not fields) in the subreport and those in the main report.

 

[aljameelah]

Thank you for your very helpful example vbaInet