Crosstab Query does not work in report format

[Dophia]

Hi: Can someone guide me with this Crosstab Query! I have a query named QryInvList and then a Crosstab Query based on the first query named QryInvList_Crosstab1.

The QryInvList_Crosstab1 works fine, as a query, but I want to put it in a report format so that I can add totals to the footer. The report is fine, except that I want to put in a date range and there is where the trouble begins.

The QryInvList_Crosstab1's SQL view is as follows (and it works great):

PARAMETERS [Enter Start Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Sum(QryInvList.tblInvoice_details_Fee) AS SumOftblInvoice_details_Fee
SELECT QryInvList.Invoice_No, QryInvList.Invoice_Date, QryInvList.People_ID, Sum(QryInvList.tblInvoice_details_Fee) AS [Total Of tblInvoice_details_Fee]
FROM QryInvList
WHERE (((QryInvList.Invoice_Date) Between [Enter Start Date] And [Enter Ending Date]))
GROUP BY QryInvList.Invoice_No, QryInvList.Invoice_Date, QryInvList.People_ID
PIVOT QryInvList.Income_Category;

When I open the report and enter the dates that I am prompted for, I then get the error:
"The Microsoft Jet database engine does not
recognize " as a valid field name or expression".

I can't figure this out and where the " comes from.

Any help would be appreciated.
Sophia

 

[lespaul]

why not put the date parameters in the source query instead of the cross tab query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lespaul]

plus you probably need the date delimiters # # around your date values.

 

[Dophia]

Thanks for both of your suggestions. I put the date parameters in the source query, but I still get the same error message when I run the report. I don't know where the date delimiters would go, could you please elaborate?
Thanks, Sophia

 

[dhookom]

It shouldn't make any difference where you place the parameters. Do you have any records with no value in the QryInvList.Income_Category column?

BTW: I don't believe you should be using parameter prompts. Controls on forms increase the functionality and ease of use of your applications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Yes, there would be fields without data.

So, would I put the query parameters in an unbound form?
Sophia

 

[dhookom]

If you are going to "PIVOT QryInvList.Income_Category" then you must either filter out the Null values or use something like Nz(QryInvList.Income_Category,"None Listed").

There are lots of resources on how to use a control on a form for filtering a query. The control on the form should be unbound.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Thank you for your advise, I'll try it.
Sophia