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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick DangerAuthor Commented:
Ron
Nice solution, thanks! No need for an explanation, it all makes sense.

Rick
0
IrogSintaCommented:
You're welcome, Rick :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.