Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    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:

    [Forms]![frmWeeklyData]![fraMonths]

    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:

    rst.Open _
    Source:="qfrmWeeklyData", _
    ActiveConnection:=CurrentProject.Connection, _
    Options:=adCmdTable

    I guess I need to mention the parameter here somewhere, but I haven't been able to find the syntax anywhere.

    TIA.

    Tom Ewald
    ewaldt@gdls.com

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    way not change the first query to a Mk table them get the cross tab to run off the new table

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Mk - Table"
    DoCmd.SetWarnings True
    DoCmd.OpenReport "Weekly report"
    hope this help

    See clear as mud


    StePhan McKillen
    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.

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Tom,
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •