Create a Report Based On a Parameter Query

Question

I have an Access application that has been running for years now. It's a payroll system. There's a query (let's call it Query3) that pulls data from 2 crosstab queries. Everything worked fine until when I introduced a parameter in the crosstab queries. The parameter refers to a date on a form. Now I can't create a report with Query3 as its record source. When I use Report Wizard and I select Query3 as its record source the field names are not displayed. I have a sub that programmatically creates a report based on Query3 but now with the parameter this fails too. I'm really stumped. Is there any workaround? Thanks in advance for your help.

Answer 1

Are you using the Forms!MyForms!MyControl syntax as the parameter in the Crosstab subs? The problem seems to be related to Access not finding the control . One thing I have done in the past is, if possible, is to remove the crosstab parameters and instead set the SQL of the crosstabs before running the report. So for example:

CurrentDB.QueryDefs("CrossTabSubName").SQL - "SELECT * FROM TABLE WHERE MyParam = 'A'"

If you have a button or something that launches the report, this may be an convenient place to do this. If the parameter is on the same form (i.e. a report filter), then this approach is simple to implement. In this example, the value of 'A' is a control on the form that allows th user to filter the report.