Query Works But Report Fails

[Guest]

Hello:

I created an XTAB query that shows the class status [student
registration-attendance-] for each class we taught this year. The query works
perfectly, however, when I use the wizard to create a report based upon the
same query, it fails. The XTAB query uses fields in a table called tblLink.
When I try to Preview the report, it says the JET engine does NOT recognize
the field:
tblLink.Class_Date.

This is bizzare because that's obviously one of the fields in the table.
Does anyone have an idea why this is happening?

Here's the query:

TRANSFORM Count(tblLink.ClassNo) AS CountOfClassNo
SELECT tblLink.CourseName, tblLink.Class_Date, Count(tblLink.ClassNo) AS
[Total Of ClassNo]
FROM tblLink
WHERE (((tblLink.Class_Date)>#12/31/2006#))
GROUP BY tblLink.CourseName, tblLink.Class_Date
ORDER BY tblLink.Class_Date
PIVOT tblLink.Attend_Status;


Thanks,
Robert

 

[Guest]

Reports and forms often have problems with crosstab queries as the column
headings can change. For example if you have a Class_Date of 1/1/2008 in the
crosstab, but the report doesn't have such a field, you get an error.

There is code out there to dynamically change a report to match the results
of a crosstab. Google for Access Crosstab Reports.

 

[Guest]

Hello Jerry:

I searched for, but couldn't find the code you referred to. However, you
gave me an idea that might work. I'm going to manually enter fixed names for
all of the columns in the Queries properties box to see if that solves the
problem.

Robert

Reports and forms often have problems with crosstab queries as the column
headings can change. For example if you have a Class_Date of 1/1/2008 in the
crosstab, but the report doesn't have such a field, you get an error.

There is code out there to dynamically change a report to match the results
of a crosstab. Google for Access Crosstab Reports.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello:

I created an XTAB query that shows the class status [student
registration-attendance-] for each class we taught this year. The query works
perfectly, however, when I use the wizard to create a report based upon the
same query, it fails. The XTAB query uses fields in a table called tblLink.
When I try to Preview the report, it says the JET engine does NOT recognize
the field:
tblLink.Class_Date.

This is bizzare because that's obviously one of the fields in the table.
Does anyone have an idea why this is happening?

Here's the query:

TRANSFORM Count(tblLink.ClassNo) AS CountOfClassNo
SELECT tblLink.CourseName, tblLink.Class_Date, Count(tblLink.ClassNo) AS
[Total Of ClassNo]
FROM tblLink
WHERE (((tblLink.Class_Date)>#12/31/2006#))
GROUP BY tblLink.CourseName, tblLink.Class_Date
ORDER BY tblLink.Class_Date
PIVOT tblLink.Attend_Status;


Thanks,
Robert

 

[Guest]

In case your idea becomes too much trouble, here's a solution from our own
Roger Carlson:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=CrossTabReport.mdb
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello Jerry:

I searched for, but couldn't find the code you referred to. However, you
gave me an idea that might work. I'm going to manually enter fixed names for
all of the columns in the Queries properties box to see if that solves the
problem.

Robert

Reports and forms often have problems with crosstab queries as the column
headings can change. For example if you have a Class_Date of 1/1/2008 in the
crosstab, but the report doesn't have such a field, you get an error.

There is code out there to dynamically change a report to match the results
of a crosstab. Google for Access Crosstab Reports.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello:

I created an XTAB query that shows the class status [student
registration-attendance-] for each class we taught this year. The query works
perfectly, however, when I use the wizard to create a report based upon the
same query, it fails. The XTAB query uses fields in a table called tblLink.
When I try to Preview the report, it says the JET engine does NOT recognize
the field:
tblLink.Class_Date.

This is bizzare because that's obviously one of the fields in the table.
Does anyone have an idea why this is happening?

Here's the query:

TRANSFORM Count(tblLink.ClassNo) AS CountOfClassNo
SELECT tblLink.CourseName, tblLink.Class_Date, Count(tblLink.ClassNo) AS
[Total Of ClassNo]
FROM tblLink
WHERE (((tblLink.Class_Date)>#12/31/2006#))
GROUP BY tblLink.CourseName, tblLink.Class_Date
ORDER BY tblLink.Class_Date
PIVOT tblLink.Attend_Status;


Thanks,
Robert

 

[Guest]

Hi Jerry:

I'm so busy today that I don't have time to look at Roger's solution, but I
will definitely check it out tomorrow or Friday. I appreciate you taking the
time to find Roger's solution for me.

Well here's the good news Jerry, creating fixed columns worked! I went into
the query's properties column and manually typed in the name of several
columns such as "Attended", "No Show", etc. and that did the trick. So now
the report reflects the same data as the query. Thanks for giving me the idea
to do such.

Robert