Parameter Form with Crosstab Queries

[Spider]

I have attached a sample of a database that Hans helped me with. I have been building upon the ideas of it but have run into 2 problems that I’m not sure how to handle.

I have done these things many times but I think I’m having trouble because we are using 2 underlying crosstab queries

I have the reports built – but I now see that I will need to be able to
1. limit data via a date range (parameter from a form) [BiannualEndDate]
2. limit the physician’s practice name (parameter from a form) [PracticeID] (in order to run a report for one physician office at a time)

The parameter form is using two text fields for the dates “between”.
It is using a list box for the physician office names.

I’ve tried using [Forms]![frmReports]![Practice] in the queries for the Practice ID, and ‘Practice’ is the name of the list box on the form. I am using the two text boxes for the date ranges for [BiannualEndDate] and they are called ‘BeginDate’ and ‘EndDate’.

My real tables for Physician and Practice are linked –so for this sample database, I have mocked it up a little with the physician info to include the PracticeID which is similar to what I actually have.

The trouble I am having is:
1. I cannot get the date to work in the crosstab queries so that I can use a date in the parameter form (I can’t find a way to use the date for the parameter).
2. [Forms]![frmReports]![Practice] is not working even if I add PracticeID to the two underlying crosstab queries –or to the qryNumDen.

Is this doable – or could you suggest any other way to limit the data? Because as the CY quarters continue, I will end up with years of data, which will be good for trending – but for this report, I will only need to see recent quarters. Every Practice (of 21) is starting at different reporting periods so I need to run them separately.

[HansV]

I don't see any form with BeginDate and EndDate in the database that you attached...

[Spider]

So sorry

[HansV]

It's possible, but it's rather nasty. You have to define the date parameters of the crosstab queries explicitly in the Parameters dialog, and you have to specify the column headings explicitly. This means that you'll have to update the column headings when data for new quarters are added.
Otherwise (I think I mentioned this in the previous thread), you'd have to write a lot of complicated VBA code.

See the attached version.

Hans PCMH Report Tracking.zip

[Spider]

Thanks so much! I will look this over.
Vicky

[Pat]

The complicated VBA code you mentioned is a great way to dynamically create a report. I used to have that code but in my purchase of a new laptop a year ago, I have lost it.
Have you got a sample database with it in please Hans?

[HansV]

Hi Pat,

See Re: Dynamic cross-tab (and report)

[Pat]

That's the one, thank you Hans.