We help IT Professionals succeed at work.

Microsoft Access crosstab query

Rick Danger
Rick Danger asked
on
In the attached database, I have created a crosstab query "qry_crosstab_parking_levels" which is the record source for the report "rpt_crosstab_parking_levels". In the query, if I leave it as it is, the report works fine, but if I set the criteria of either "display" fields to "True" then the report fails. So how do I make it so that the report works in both cases?
scheduleDB---Copy.zip
Comment
Watch Question

That's because setting it to True removes "Level 20 Ground FFL" from the source and yet you have a control on your report specifically bound to this field.  You have two choices:

Make all the possible levels show up on your report whether or not that field has data (this could make for a very wide report).
Make your report dynamic (via VBA code) to revise the ControlSources for your report controls to match those in your query.


Ron

Author

Commented:
Ron
I realise why it was doing it, but I need to know how to get round the problem. The VBA solution sounds interesting, but can you supply the code please? Or anybody else?
Well what would be the maximum number of columns you would show?  For instance, if your query returns 15 levels, will your report be able to fit all 15?

Ron

Author

Commented:
Let's assume a maximum of 8
So if you're only allowing 8 columns on your report, and your query returns 10 columns, what's the plan?

Author

Commented:
Surely it won't. If I am assuming a maximum of only 8 columns from the query, we'll only need 8 columns on the report. As I don't know the VBA solution yet, I can't tell you the plan. But let's assume it  won't happen.
Here's your revised database with code added on the report's OnOpen event.  Just add more controls (labels and textboxes) for the other columns and name them accordingly just like I named the others.  For instance, the names for column 8's textbox and label will be called lblLevel8 and txtLevel8.

Ron
scheduleDB---Copy.accdb

Author

Commented:
Ron
Nice solution, thanks! No need for an explanation, it all makes sense.

Rick
You're welcome, Rick :-)