Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Queries (2000)

    Hopefully another easy question to solve....

    How do I put criteria into my crosstab query? I have a few fields in a form that I want to pass to my query, but when I put that in my query, it says that the Microsoft Jet database engine does not recognize my criteria as a valid field name or expression?

    Here's the query:

    TRANSFORM Sum(DBSTATS.STAT) AS SumOfSTAT
    SELECT tblDatabases.DatabaseName, DBSTATS.YEAR, TYPE.TYPE_DESCRIPTION, DBSTATS.TYPE, Sum(DBSTATS.STAT) AS [Total Of STAT]
    FROM (DBSTATS INNER JOIN tblDatabases ON DBSTATS.DATABASE_ID = tblDatabases.DatabaseID) INNER JOIN TYPE ON DBSTATS.TYPE = TYPE.TYPE_ID
    WHERE (((DBSTATS.DATABASE_ID)=[Forms]![frmREPORTS]![cboDatabaseID]) AND ((DBSTATS.YEAR)=[Forms]![frmREPORTS]![cboYEAR]) AND ((DBSTATS.TYPE)=[Forms]![frmREPORTS]![txtType]))
    GROUP BY tblDatabases.DatabaseName, DBSTATS.YEAR, TYPE.TYPE_DESCRIPTION, DBSTATS.TYPE
    PIVOT DBSTATS.MONTH;

    I am positive that the parameters are spelled correctly and everything. Thanks in advance!
    Kindra

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Queries (2000)

    Try declaring the parameters explicitly:
    - Open the query in design view.
    - Select Query | Parameters...
    - In the Parameter column, enter [Forms]![frmREPORTS]![cboDatabaseID], and in the Data Type column, select the appropriate data type.
    - Repeat for [Forms]![frmREPORTS]![cboYEAR] and [Forms]![frmREPORTS]![txtType].
    - Click OK.
    You *must* enter the parameters exactly as they occur in the criteria line. Copying and pasting is a way to ensure that.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Queries (2000)

    Unfortunately that didn't work. At first I kept the other parameters in, which didn't work. I got an error message that said "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of te expression to variables".

    Then I took those out and just had the explicitly declared parameters and it basically ignored the parameters. It prompts me for them, but doesn't recognize them when I run the query. Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Queries (2000)

    Which other parameters?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Queries (2000)

    This part of the query:
    WHERE (((DBSTATS.DATABASE_ID)=[Forms]![frmREPORTS]![cboDatabaseID]) AND ((DBSTATS.YEAR)=[Forms]![frmREPORTS]![cboYEAR]) AND ((DBSTATS.TYPE)=[Forms]![frmREPORTS]![txtType]))

    I tried deleting the [Forms]... part entirely but it didn't work. Should I delete the entire Where clause?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Queries (2000)

    In the Query | Parameters dialog, you only tell Access which parameters you are using, and of which type they are. The Criteria row (the WHERE part in the SQL) tells Access how they are used.

    You shouldn't take the WHERE clause out, for that would defeat the purpose of your query. Instead, check carefully whether you have used exactly the same expressions in the Criteria row and in the Parameters dialog, and whether you have used the correct data types.

    A crosstab query with explicitly declared parameters should work.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Queries (2000)

    Thank you once again Hans!

    Kindra

Posting Permissions

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