Report based on a crosstab query (Access 97)

Topic

New Reply

I have a report based on a crosstab query that shows the number of items in a department. The report works fine as long as all the departments show up in the query. However if a department does not show up in the query, then I get an error message that says the Microsoft Jet database engine does not recognize the control name on the report as a valid field name or expression and does not run the report.
Does anybody have a fix for this so I can get the report to run.
Thanks
JimB

Reply | Quote

Replies

Create a table with a list of departments then do a query with an outside join from the departments table to the query that you are running the crosstab query on. This should select all departments.

Then create the crosstab query on your new query.

Not sure whether this will work, but its what I would try first

Reply | Quote

If your departments do not change you can at on to the end of your SQL for the crosstab query just before the semi-colin “IN(Department One, Department Two, Department Three);” etc. without the quotes. This will create a column for each department regardless of the existance of data.

Hope this helps.

Warren

Reply | Quote

>>just before the semi-colin “IN(Department One, Department Two, Department Three);”

Is this part of a WHERE clause as in “… WHERE DeptNo In(…)”

Reply | Quote

Hi,

No, it would be added to the end of his existing crosstab query to create the columns (fields) he needs for the report. Here is a copy of one from one of my databases:

TRANSFORM First(StudentWorkQuery.WorkDescription) AS FirstOfWorkDescription
SELECT StudentWorkQuery.Student_PersonID, StudentWorkQuery.FirstName, StudentWorkQuery.LastName
FROM StudentWorkQuery
GROUP BY StudentWorkQuery.Student_PersonID, StudentWorkQuery.FirstName, StudentWorkQuery.LastName
PIVOT StudentWorkQuery.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

I needed numbers instead of the departments wanted here.

Hope this helps.

Warren

Reply | Quote

Thanks for clearing that up. This is a very useful trick, and I wish I had known about it before!

Reply | Quote

I have had odd behaviour at times from Access when I define output field names as a number (such as 1, 2…). I always use D1, D2… so that it is clear that it is a column name.
If I remember correctly it was in adressing an output column through code such as RS![1].

Reply | Quote

That will work to get a label for a column but the problem I am having is with the text control. There is a text control for each department and when there is no data for a department I get the error message. I need to have the text control show blank if there is no data.
Thanks,
JimB

Reply | Quote

Hi,

Maybe I am misunderstanding. Sorry. I thought you were getting the error on the report when there was no data. This error would be true since there is not a column (Field) for the data in the crosstab query. If the crosstab query has the department column and this is used on the report for the Field there shold be no error even with no data. I am using Access 2000, but I don’t think this is different for 97.

You can try the NZ function in the report field and see if this helps. I don’t know if 97 has the NZ??

=NZ([Text265],0)

Sorry, I don’t remember 97 better.

Warren

Reply | Quote

You didn’t misunderstand me, I just didn’t understand what you were telling me. I finally entered the department numbers in the In statement and the report works great.
Thanks for the help,
JimB

Reply | Quote