Hiding number of report columns based on a value 1

[jodjim]

I have a report with months 1 to 12 as columns in Detail section. What's the best way of hiding the columns 1 to 5, for example, if the desired information (input selection from Form) is only from Jan to May. Initially, I was thinking of use Select Case making each month visible only as applicable, but it looks like a very long Case statements. Thanks for the help.

 

[lameid]

In general terms, I would set the visible property of the columns to true of false in code on the on format event.

It sounds like you are using a cross tab query as a recordsource.

I would specify the column headings to be 1 to 12 and then put the criteria in the query. Then if it is only run for a date range, only the related months will contain data.


If that is not helpful, then the SQL (or equivalent) of the recordsource of your report and additional information will be helpful.

 

[jodjim]

You're guess it right, I'm using cross tab query. For testing purposes, I created a cross tab query for Months 1 to 5 and make it as my record source for the report. The report has a layout with 12 months in page header and in detail. When the report is opened, it gives out error message for the fields for months 6 to 12 since they're excluded in the query. How do I resolve this?

Appreciate your help.

 

[dhookom]

You might want to consider the method described in faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jodjim]

The header is now taken cared of. Thanks for the tip. Now, I still stuck with the details. I tried putting a condition say for Month6 with total months selected is only 5, IIF(totMonth>=6,[Month6],""), and so on with months 7 to 12. It's giving out error msg "The Microsoft Jet database engine does not recognize 'Month6' as a valid fieldname or expression". And it's because my source has only up to month 5 based on my crosstab query. How do I fix it? Thanks.

 

[dhookom]

Did you set the Column Headings property as suggested in the FAQ? What is the SQL view of your query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

I would specify the column headings to be 1 to 12 and then put the criteria in the query. Then if it is only run for a date range, only the related months will contain data.

What I mean is open the query. For your column heading, right click the field row and hit properties. For the column hading enter the possible values.

i.e. 1,2,3,4,5,6,7,8,9,10,11,12

Then even if your data does not create the columns, it will be int the result.

Or you could try to do it more generically like the FAQ but I think this will be quicker (and less versatile).

 

[jodjim]

Got it! One last, little thing. How do I make the column total movable so it will be right after the last month column?

Thanks again, here's a star for you!

 

[dhookom]

A crosstab query won't allow you to move a Row Heading generated column to the right. This is a report's forum so you just move the bound controls to the right.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]