Crosstab

[Steve]

I have a crosstab query that works well. When I use it as the record source of a report it only works on one set of criteria. I have referenced a control on a form as my "where" criterian in the query grid as well as in the parameter dialog box. Any suggestions?

 

[Duane Hookom]

We have no idea without knowing what isn't working the way you expect it to
work. Can you provide more details, perhaps the SQL of the query?

--
Duane Hookom
MS Access MVP

I have a crosstab query that works well. When I use it as the record

source of a report it only works on one set of criteria. I have referenced
a control on a form as my "where" criterian in the query grid as well as in
the parameter dialog box. Any suggestions?

 

[Steve]

Duane
I don't know if my problem is in the query, report or form. When I run my query without the report it works fine. When I run it from my report it only works when [TName] = "a specific name", otherwise it gives this error message: The Microsoft Jet database engine does not recoginize " as a valid field name or expression. I am puzzled by the single quoation mark

Here is my SQL

PARAMETERS [Forms]![TrialLocation]![TName] Text ( 255 )
TRANSFORM First(PlotInfo.Plot) AS FirstOfPlo
SELECT PlotInfo.FieldRange, [Trial Locations].LocationName, [Trial Locations].GrowerNam
FROM (PlotInfo INNER JOIN Trials ON PlotInfo.Name = Trials.Name) INNER JOIN [Trial Locations] ON Trials.LocationValue = [Trial Locations].KeyLocatio
WHERE ((([Trial Locations].LocationName)=[Forms]![TrialLocation]![TName])
GROUP BY PlotInfo.FieldRange, [Trial Locations].LocationName, [Trial Locations].GrowerNam
ORDER BY PlotInfo.FieldRange DESC
PIVOT PlotInfo.FieldRow

 

[Duane Hookom]

Are you closing the form when running the report?

--
Duane Hookom
MS Access MVP
--

Duane,
I don't know if my problem is in the query, report or form. When I run my

query without the report it works fine. When I run it from my report it
only works when [TName] = "a specific name", otherwise it gives this error
message: The Microsoft Jet database engine does not recoginize " as a valid
field name or expression. I am puzzled by the single quoation mark.

Here is my SQL:

PARAMETERS [Forms]![TrialLocation]![TName] Text ( 255 );
TRANSFORM First(PlotInfo.Plot) AS FirstOfPlot
SELECT PlotInfo.FieldRange, [Trial Locations].LocationName, [Trial Locations].GrowerName
FROM (PlotInfo INNER JOIN Trials ON PlotInfo.Name = Trials.Name) INNER

JOIN [Trial Locations] ON Trials.LocationValue = [Trial
Locations].KeyLocation

WHERE ((([Trial Locations].LocationName)=[Forms]![TrialLocation]![TName]))
GROUP BY PlotInfo.FieldRange, [Trial Locations].LocationName, [Trial Locations].GrowerName
ORDER BY PlotInfo.FieldRange DESC
PIVOT PlotInfo.FieldRow;

 

[Duane Hookom]

If the query runs fine and the form is open, then I would expect that you
have an un-known expression in the Sorting and Grouping or Code or a Control
in the report. Are you using code to display your crosstab results?

--
Duane Hookom
MS Access MVP
--

The report behaves the same way wether I run it in design view, from the

object list, or from the command button on my form.

 

[Steve]

I'm not using code

I can reproduce the error in Northwinds with the following query SQL

PARAMETERS Country Text ( 255 )
TRANSFORM Count(Orders.OrderID) AS CountOfOrderI
SELECT Customers.Countr
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerI
WHERE (((Customers.Country)="Austria")
GROUP BY Customers.Countr
PIVOT Customers.CompanyName

My report has 3 text boxes in the detail section: control sources for the 3 are "Country";"Alfreds Handel"; and "Ernst Handel"

 

[Duane Hookom]

You could add Column Headings to the crosstab query.
PIVOT Customers.CompanyName IN ("Alfreds Handel","Ernst Handel");

If your crosstab is dynamic (column headings can vary) how are you handling
this with your report?

--
Duane Hookom
MS Access MVP

I'm not using code.

I can reproduce the error in Northwinds with the following query SQL:

PARAMETERS Country Text ( 255 );
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Customers.Country)="Austria"))
GROUP BY Customers.Country
PIVOT Customers.CompanyName;

My report has 3 text boxes in the detail section: control sources for the

3 are "Country";"Alfreds Handel"; and "Ernst Handel"

 

[Steve]

Duane, That fixed it. My report was not dynamic therefore only one criterian fit the report the rest returned error. I still think the error message was ambigious.
Thanks a million for all your help.