No data from crosstab query

[kwor]

I have a couple of related tables.
I have a form based on those tables that provides parameter input to a select query.
I have a crosstab query based on the select query.
This all works. I can select the required parameter on the form and view the results in the crosstab query.

I used to have a report based on the crosstab query. This used to work. It doesn't any more, instead it reports: The MSO Access database engine does not recognize " as a valid field or expression.

I opened the form, selected a criteria for the parameter selection and then opened the report in design mode - all the data fields display the little green "tick" representing no data.

I went to the report wizard, selected the crosstab query to find no fields available!!
I selected manual design and found the fields available!!

Can someone give me some hints about finding the problem.

 

[dhookom]

How about providing the SQL view of the query? Are you closing the form at any time with code? Does your report run code?

I expect you might have a null value in one of the Column Headings.


Duane
Hook'D on Access
MS Access MVP

 

[kwor]

The form stays open.
There is no code in the report.

"Null value in the column heading" Hmmm.. I will check that for the original report because some "fixed" data may have changed.

However, I would still like to understand why the report wizard would not display any data fields when trying to create a new report. Also why the existing report shows "little green ticks" indicating no related data fields.

The SQL code for the select query is:

PARAMETERS [Forms]![frmReport_OOT_Placements]![Year] Long;
SELECT tblPlacements.PID, tblAreas.Area, tblAreas.GT50Km, tblPlacements.IntExt, tblPlacements.Code
FROM tblAreas INNER JOIN tblPlacements ON tblAreas.Name = tblPlacements.Name
WHERE (((tblPlacements.Year) Like [Forms]![frmReport_OOT_Placements]![Year]))
ORDER BY tblPlacements.Code;

The SQL code for the crosstab is:

TRANSFORM Count([qryOOT-List].PID) AS CountOfPID
SELECT [qryOOT-List].GT50Km, [qryOOT-List].IntExt, [qryOOT-List].Code, Count([qryOOT-List].PID) AS [Total Of PID]
FROM [qryOOT-List]
GROUP BY [qryOOT-List].GT50Km, [qryOOT-List].IntExt, [qryOOT-List].Code
PIVOT [qryOOT-List].Area;

 

[dhookom]

First, change the select query to:

PARAMETERS [Forms]![frmReport_OOT_Placements]![Year] Long;
SELECT tblPlacements.PID, tblAreas.Area, tblAreas.GT50Km, tblPlacements.IntExt, tblPlacements.Code
FROM tblAreas INNER JOIN tblPlacements ON tblAreas.Name = tblPlacements.Name
WHERE tblPlacements.Year Like [Forms]![frmReport_OOT_Placements]![Year]
AND tblAreas.Area is not Null
ORDER BY tblPlacements.Code;

Why are you using Like when there doesn't seem to be a wild card character in the expression? I would think you would use =.

Then, I would add all possible values of Area into the Column Headings property of the Crosstab query.


Duane
Hook'D on Access
MS Access MVP

 

[kwor]

I changed the select query as per your recommendations. No change. The "Report wizard" still has no data fields but they are available via "Report Design". My original report still returns the same error.

"Like" or "=" makes no difference.

"add all possible values of Area into the Column Headings property of the Crosstab query" - I'm not sure what you mean by this, could you please explain further because I think this is where the problem lays. The original report was designed for a fixed number of Areas. However, users have entered more Areas. I gather this may be what caused the original error. It would seem that my report may need a dynamic number of columns...