Parameter problem with Report using Crosstab Query.

[Guest]

Hello all,

In my inexperience and probably unconventional ways, I created a crosstab
query that has this SQL statement.

PARAMETERS [Which year?] Short;
TRANSFORM Sum(Production_chooseyear.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_chooseyear.ModelID, Production_chooseyear.LineID,
Styles.StyleType, Sum(Production_chooseyear.QtyProduced) AS [Total Of
QtyProduced]
FROM (Styles RIGHT JOIN (CoolerTypes RIGHT JOIN Models ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID) RIGHT JOIN Production_chooseyear ON Models.ModelID =
Production_chooseyear.ModelID
WHERE (((Models.CoolerTypeID)<>9999 And (Models.CoolerTypeID)<>9999))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_chooseyear.ModelID, Production_chooseyear.LineID, Styles.StyleType
PIVOT "Qtr " & Format([ProductionDate],"q");

Basically it is a Crosstab qry that sums production by Qtr, and has a
paramenter value that pops up so the user can pick which years data it should
include.

When I run this qry from the qry menu it seems to work correctly.

BUT, I have a report that was based on this qry before I added the
parameter value to select which year of data. I had no problems with it.

Now, when I try to run the report, or just view it in design view, I get the
parameter value dialog box over and over and over again. This prevents me
from making any changes in design veiw, or veiwing the report.

Is there a better way of going about this?

 

[John Spencer]

What you posted does not even use the parameter. So I don't see any reason
to have it.

Try removing the Parameter declaration and see if you can open the report.
If you still get requests for Which Year then the problem is somewhere in
the report.

Are you by any chance using a sub-report in the report?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks for the quick response. I think I solved that problem, but it still
isn't going to work.

Let me start from the beginning and see if you can lead me in the right
direction, because the route I was taking isn't going to work.

I have a crosstab query that sums production qtys by quarter. I have a
report that uses this queary to compared one quarters production numbers to
the previous qtr. (there is no subreport)

Right now, I have a parameter in the query that filters the production data
to the current year.

So, the report I am working on today, shows 2nd qtr production compared to
3rd qtr production. I can run it for 2005, 2006, 2007 etc using the
parameter dialog box...good!

My problem is this...

-what happens in March of next year when I want to conpare 1st qtr 2008 to
4th qtr 2007?

Instead of filtering by year I guess I need to set a beginnng and end date
parameter (i know how to do this), but is there an easier way about this?

What you posted does not even use the parameter. So I don't see any reason
to have it.

Try removing the Parameter declaration and see if you can open the report.
If you still get requests for Which Year then the problem is somewhere in
the report.

Are you by any chance using a sub-report in the report?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello all,

In my inexperience and probably unconventional ways, I created a crosstab
query that has this SQL statement.

PARAMETERS [Which year?] Short;
TRANSFORM Sum(Production_chooseyear.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_chooseyear.ModelID, Production_chooseyear.LineID,
Styles.StyleType, Sum(Production_chooseyear.QtyProduced) AS [Total Of
QtyProduced]
FROM (Styles RIGHT JOIN (CoolerTypes RIGHT JOIN Models ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID) RIGHT JOIN Production_chooseyear ON Models.ModelID =
Production_chooseyear.ModelID
WHERE (((Models.CoolerTypeID)<>9999 And (Models.CoolerTypeID)<>9999))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_chooseyear.ModelID, Production_chooseyear.LineID,
Styles.StyleType
PIVOT "Qtr " & Format([ProductionDate],"q");

Basically it is a Crosstab qry that sums production by Qtr, and has a
paramenter value that pops up so the user can pick which years data it
should
include.

When I run this qry from the qry menu it seems to work correctly.

BUT, I have a report that was based on this qry before I added the
parameter value to select which year of data. I had no problems with it.

Now, when I try to run the report, or just view it in design view, I get
the
parameter value dialog box over and over and over again. This prevents me
from making any changes in design veiw, or veiwing the report.

Is there a better way of going about this?