Unanswered: Declaring parameters in crosstab query with ADO
I am running into a problem with a cross tab query. I have a report based on a parameter query, which is in turn based on a cross tab query (which is based on the same parameter), and ADO doesn't seem happy. On MSFT's site, they use DAO in their (very complex) example, and they show how to specify a parameter (qdf = a QueryDef, qdf.Parameters(xxx) = yyy, etc.). I really prefer to use ADO (Using Access 2002, and I'm trying to learn ADO), but I don't see how to specify a parameter's value in ADO. I'm assuming that's the problem, because Access keeps saying that I'm not specifiying required info. Also, when I went through and replaced all instances of parameters in the queries (query based on queries based on queries) with solid numbers, it worked. In the actual queries, the parameter is:
This simply refers to a frame containing option buttons so that I can specify the month I'm interested in. The month's number is then used by the queries.
Running the queries without the report works just fine. However, since a cross tab query is involved, and there can be varying numbers of columns, I have to use dynamic columns in the report, and that's where complexity rears its ugly head. The parameter query (that calls the cross tab query) is necessary because I have information in addition to the cross tab query itself which is needed in the report.
Here's the code portion (behind the report) that Access highlights:
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
I assume that "qfrmWeeklyData" is the final query that the report is based on? And that somewhere before this query, is the query that needs to know what month the user wants the report to display.
There are a couple of ways to get the month into the SQL for that query. For me, the best is to have "variable" within the SQL that you can find with VBA, and just do a Replace() of that "variable" with the month number. Of course you would be using DAO to do this. You might want to look at this link. It refers to ADO queries within Access.
If after reading this artical, you want to use DAO to put the desired month into it, post back and I, or someone else, can walk you through it.