Crosstab Query not working with FORM

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 http://screencast.com/t/XvawZhC8Kan

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

Any thoughts?
EECrossTab1.accdb
Patrick O'DeaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
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])
0
Jeffrey CoachmanMIS LiasonCommented:
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

JeffCoachman
EECrossTab1.accdb
0
Patrick O'DeaAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.