Error in Reporting from Crosstab Query

[Guest]

I am trying to generate a report from a Crosstab Query and I have a few
questions and a couple of problems. First, I want to use a form to prompt a
user-input date range for the report to be generated over. I have created
the parameters in the Query->Parameters area as [Forms]![Report
Creator]![BeginningDate] and [Forms]![Report Creator]![EndingDate]. I have
also put in the line in the criteria area under the date column in the
crosstab query Between [Forms]![Report Creator]![BeginningDate] And
[Forms]![Report Creator]![EndingDate]. Here is my SQL for this query...

PARAMETERS [Forms]![Report Creator]![BeginningDate] DateTime,
[Forms]![Report Creator]![EndingDate] DateTime;
TRANSFORM
Avg((AuditDetails![#Inspected]-AuditDetails![#Failed])/AuditDetails![#Inspected]) AS PassRate
SELECT Location.Line, AuditDetails.AuditType
FROM (Identification INNER JOIN Location ON
Identification.IdentificationID=Location.IdentificationID) INNER JOIN
AuditDetails ON Location.LocationID=AuditDetails.LocationID
WHERE (((Identification.Date) Between Forms![Report Creator]!BeginningDate
And Forms![Report Creator]!EndingDate))
GROUP BY Location.Line, AuditDetails.AuditType, Identification.Date
PIVOT Location.Shift;

I have the report tied to a macro that opens the form [Forms]![Report
Creator] just like in the directions given in the help menu for query by
form. Two things happen. Before the form opens, enter parameter pop up
boxes asking for [Forms]![Report Creator]![BeginningDate] and [Forms]![Report
Creator]![EndingDate]. Once those are entered the form appears. In the
BeginningDate blank, if I enter any date other than the first date for which
I have data, the following error appears and no report will be generated...

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

I have checked everything for spelling errors and have found none. What is
causing this, and what can I do to get these reports to compile properly?
Thanks in advance.

 

[Duane Hookom]

I always start with the form open. Fill in the correct dates and then click
a button that opens the report.

I noticed one possible issue with the crosstab SQL view. I think you want to
remove ", Identification.Date" from the Group By clause.

 

[Guest]

For a report you need to know the column headings or it will bomb out. There
are two ways of doing this. The first is to prepopulate the Column Headings
property of the crosstab query. That way the text boxes on the reports will
always know where to find the data. It has other benefits such as putting the
columns in a order that you like (such as months in chronological order
instead of April, August, etc.). It will also hold open a field such as for a
month that had no data or exclude a month if you don't want to see June for
example.

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

I am trying to generate a report from a Crosstab Query and I have a few
questions and a couple of problems. First, I want to use a form to prompt a
user-input date range for the report to be generated over. I have created
the parameters in the Query->Parameters area as [Forms]![Report
Creator]![BeginningDate] and [Forms]![Report Creator]![EndingDate]. I have
also put in the line in the criteria area under the date column in the
crosstab query Between [Forms]![Report Creator]![BeginningDate] And
[Forms]![Report Creator]![EndingDate]. Here is my SQL for this query...

PARAMETERS [Forms]![Report Creator]![BeginningDate] DateTime,
[Forms]![Report Creator]![EndingDate] DateTime;
TRANSFORM
Avg((AuditDetails![#Inspected]-AuditDetails![#Failed])/AuditDetails![#Inspected]) AS PassRate
SELECT Location.Line, AuditDetails.AuditType
FROM (Identification INNER JOIN Location ON
Identification.IdentificationID=Location.IdentificationID) INNER JOIN
AuditDetails ON Location.LocationID=AuditDetails.LocationID
WHERE (((Identification.Date) Between Forms![Report Creator]!BeginningDate
And Forms![Report Creator]!EndingDate))
GROUP BY Location.Line, AuditDetails.AuditType, Identification.Date
PIVOT Location.Shift;

I have the report tied to a macro that opens the form [Forms]![Report
Creator] just like in the directions given in the help menu for query by
form. Two things happen. Before the form opens, enter parameter pop up
boxes asking for [Forms]![Report Creator]![BeginningDate] and [Forms]![Report
Creator]![EndingDate]. Once those are entered the form appears. In the
BeginningDate blank, if I enter any date other than the first date for which
I have data, the following error appears and no report will be generated...

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

I have checked everything for spelling errors and have found none. What is
causing this, and what can I do to get these reports to compile properly?
Thanks in advance.

 

[Guest]

How do I prepopulate the Column Headings...what do I need to enter into that
blank?

Thanks.

For a report you need to know the column headings or it will bomb out. There
are two ways of doing this. The first is to prepopulate the Column Headings
property of the crosstab query. That way the text boxes on the reports will
always know where to find the data. It has other benefits such as putting the
columns in a order that you like (such as months in chronological order
instead of April, August, etc.). It will also hold open a field such as for a
month that had no data or exclude a month if you don't want to see June for
example.

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

I am trying to generate a report from a Crosstab Query and I have a few
questions and a couple of problems. First, I want to use a form to prompt a
user-input date range for the report to be generated over. I have created
the parameters in the Query->Parameters area as [Forms]![Report
Creator]![BeginningDate] and [Forms]![Report Creator]![EndingDate]. I have
also put in the line in the criteria area under the date column in the
crosstab query Between [Forms]![Report Creator]![BeginningDate] And
[Forms]![Report Creator]![EndingDate]. Here is my SQL for this query...

PARAMETERS [Forms]![Report Creator]![BeginningDate] DateTime,
[Forms]![Report Creator]![EndingDate] DateTime;
TRANSFORM
Avg((AuditDetails![#Inspected]-AuditDetails![#Failed])/AuditDetails![#Inspected]) AS PassRate
SELECT Location.Line, AuditDetails.AuditType
FROM (Identification INNER JOIN Location ON
Identification.IdentificationID=Location.IdentificationID) INNER JOIN
AuditDetails ON Location.LocationID=AuditDetails.LocationID
WHERE (((Identification.Date) Between Forms![Report Creator]!BeginningDate
And Forms![Report Creator]!EndingDate))
GROUP BY Location.Line, AuditDetails.AuditType, Identification.Date
PIVOT Location.Shift;

I have the report tied to a macro that opens the form [Forms]![Report
Creator] just like in the directions given in the help menu for query by
form. Two things happen. Before the form opens, enter parameter pop up
boxes asking for [Forms]![Report Creator]![BeginningDate] and [Forms]![Report
Creator]![EndingDate]. Once those are entered the form appears. In the
BeginningDate blank, if I enter any date other than the first date for which
I have data, the following error appears and no report will be generated...

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

I have checked everything for spelling errors and have found none. What is
causing this, and what can I do to get these reports to compile properly?
Thanks in advance.