05-14-07, 07:30 #1Registered User
- Join Date
- Apr 2006
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:
I guess I need to mention the parameter here somewhere, but I haven't been able to find the syntax anywhere.
05-14-07, 15:59 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
way not change the first query to a Mk table them get the cross tab to run off the new table
DoCmd.OpenQuery "Mk - Table"
DoCmd.OpenReport "Weekly report"hope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON
05-14-07, 21:13 #3Registered User
- Join Date
- Jan 2007
- California, USA
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.