Microsoft Access crosstab query

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
rick_dangerAsked:
Who is Participating?
 
IrogSintaCommented:
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
0
 
IrogSintaCommented:
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
0
 
rick_dangerAuthor 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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
IrogSintaCommented:
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
0
 
rick_dangerAuthor Commented:
Let's assume a maximum of 8
0
 
IrogSintaCommented:
So if you're only allowing 8 columns on your report, and your query returns 10 columns, what's the plan?
0
 
rick_dangerAuthor 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.
0
 
rick_dangerAuthor Commented:
Ron
Nice solution, thanks! No need for an explanation, it all makes sense.

Rick
0
 
IrogSintaCommented:
You're welcome, Rick :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.