Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,
    I'm trying to run a crosstab query that uses another query to provide the information. The first query is sorted by a a control [txtYear] on frmReports. This works fine. This query is a query that calculates totals.

    When I go to run the new crosstab query I get the error that I've uploaded here. I don't understand why I'm getting the error since the control filters the first query with no problem.

    [attachment=87992:crosstaberror2.gif]

    Thanks,
    Leesha
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are you creating this using the Crosstab wizard or by changing the query type to Crosstab. The error message indicates that you haven't set either the Row Heading or the Column Heading in the design. Crosstab queries add a second row (below the Totals row if it is on), and you have to specify one or more Row Headings, one Column Heading and one Value. Hope that helps.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi!

    I'm not doing it from the wizard, however I do have all of the rows, columns and values identified. Nothing I try works. I use crosstabs all the time so this one really has me stumped. It runs fine until I put in the filter of [forms]![frmReports]![txtyear]. If I don't try to filter the year its OK. If I use this same filter on a select query it runs fine and filters as it should. It's only when I try to do it in a crosstab query.

    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Try putting the [forms]![frmReports]![txtyear] in a separate cell where Year is the field and that is the criteria, and put a WHERE on that column. If that doesn't do it, you may need to put it in the Select query you are basing the crosstab on. If that stumps you, try giving us the SQL statement for your Select and your Crosstab.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you specify your parameter field as a parameter in the query properties? [forms]![frmReports]![txtyear] in the parameter side and the datatype (either date or integer depending on what is in that field). Crosstabs will fail without these specified.

    Peter

Posting Permissions

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