Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Crosstab Query not working with FORM

Posted on 2014-08-04
Medium Priority
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 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?
Question by:Patrick O'Dea
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40238648
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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 668 total points
ID: 40238653
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;

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:

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

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 664 total points
ID: 40238950
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
ID: 40242712
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

580 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