vba help in a report

[hedge]

Hi,

I currently have a report in Access 2003 that is running off of a cross tab query.

The report basically looks like this:

Oct Nov Dec
0 0 100K

It has months going accross the top and values under each month.

I am trying to use a vba code to make all the columns and headings where the value is null or zero become hiden?

Is this possible?

Thanks

[Stewart Ross]

It's not really possible to hide all the values you suggest, no.

In a crosstab query if there is a heading there is at least one non-null value in the column concerned, so you can't hide the column headings without losing data. I can think of no obvious way that you could determine whether or not a column just contained 0s from VBA within your report - you'd need to run a separate query of some kind to do this.

Even if you did manage to find out that a column was all 0's, to do anything useful with the data you'd need to set up unbound textboxes throughout your report and assign the contents of each under VBA code (not a simple task). This is because there is no facility in a report to 'hide' a column and make all others close up the gaps on the page to compensate (unlike hiding a column in Excel, where hiding a column gives a seamless result).

You can set textbox formatting so that 0 values are not displayed - look up the help for custom formats for the full details, but essentially you can use custom formatting to display an empty string in place of a zero. Null values do not show by default in textboxes in any event. Not displayed does not mean not taking up space, however - you simply have a blank value where there would have been a 0, and a blank value for every null. If you want to use the space to display the next column instead then you would really have to implement the custom unbound textbox controlled by VBA approach - and unless you really, really have to I'd avoid doing it that way.

Crosstab queries can be a problem to use as the basis for a report. The number of columns and their names may vary; this can to some extent be countered for smaller crosstabs by defining a column header list, but many crosstab queries vary too dynamically for this approach to work.

You could take a different approach, by excluding nulls and zero values from your query. You might have to experiment a bit where best to do this - before crosstabbing, in the crosstab itself, or through another query - but you will almost certainly find this much easier and much more maintainable than trying to do what you ask in VBA within your report.

Welcome to Bytes!

-Stewart