Crosstab Query not working with FORM

Posted on 2014-08-04
Last Modified: 2014-08-05
See attached which is a sample demo of my problem.

I want a "crosstab" report showing a count of all the cars in my town.
However only cars made after 1999 should be included.

1. Run FORM1 and put 1999 in for the year.
2. Then click "Crosstab Query" which calls Query1 which in turn calls Query1_Crosstab.

See error

The system seems to object to ">[forms]![Form1]![YearCheck]" in QUery1.  However, this works when run as a simple query.

Any thoughts?
Question by:Patrick O'Dea
    LVL 84

    Accepted Solution

    Your crosstab query doesn't work at all, even viewing it directly, so your issue is not related to the Form.

    You might try creating your crosstab query, and then create a NEW query based on that Crosstab that includes your criteria. So your crosstab would be base on Table1 instead of Query1.

    If that doesn't work, then you might consider using a temporary table for this purpose. Basically, you create a table in your database that matches the structure you want to see (i.e. Columns), and then fill that table in a VBA/SQL routine in your button click. You'd then base your form or report on that table.
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    From my iPad, so I cannot download your app.

    My experience is that when you create a crosstab based on another query, which is a parameter query, you should declare the parameter in both queries.  Did you declare that parameter:

    Parameters [Forms]![Form1]!YearCheck  long;
    SELECT ...

    and then in the Crosstab
    Parameters [Forms]![Form1]!YearCheck  long;
    Transform ...
    Another trick you might try is to simply wrap the parameter in the first query with the Eval function:

    SELECT ...
    FROM yourTable
    Where [ModelYear] >Eval([forms]![Form1]![YearCheck])
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    What is that "CountOfCars" Field for in Query1?
    It just displays 1 for each record?
    If you need a "Count" of the cars, you can do this in the crosstab query, directly, without this new field.

    In addition to what the other experts posted, you can also use a Public variable and skip the main query:

    In a module, do this:

    Public lngpubYear As Integer

    Public Function GetYear() As Integer
        GetYear = lngpubYear
    End Function

    ...Then your crostab query will be something like this:

    TRANSFORM Count(Table1.IDAuto) AS CountOfIDAuto
    SELECT Table1.Car, Count(Table1.IDAuto) AS [Total Of IDAuto]
    FROM Table1
    WHERE (((Table1.YearofMake)>GetYear()))
    GROUP BY Table1.Car
    PIVOT Table1.YearofMake;

    Returned, modified sample attached


    Author Closing Comment

    by:Patrick O'Dea
    Thanks all!

    I guess I did not present my question very well.

    I applied the suggestions that you made and also some common sense and resolved the issue.

    Thanks again!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now