Crosstab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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


Steve said:
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
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
 
Are you closing the form when running the report?

--
Duane Hookom
MS Access MVP
--

Steve said:
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;
 
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
--

Steve said:
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.
 
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"
 
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


Steve said:
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"
 
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.
 
Back
Top