CrossTab Query as a Report Data Source (97)

Topic

New Reply

We created a cross tab query that provides the expected results. However, when we use the query as the data source in a report it asks for parameter value for tblOther.dtmInvoice. I can’t see the forest for the trees now. The following is the SQL from the query. As stated, the query alone works fine, with no prompts. It is only when running the report that uses the query that OFI’s occur.

TRANSFORM Count(tblOther.pk_Invoice) AS [The Value]
SELECT tblOther.I_strLocation, Count(tblOther.pk_Invoice) AS [Total Of pk_Invoice]
FROM tblOther
WHERE (((Year([tblOther]![I_dtmInvoice]))=[Forms]![frm_IReports]![txtYTDYear]))
GROUP BY tblOther.I_strLocation
ORDER BY tblOther.I_strLocation
PIVOT Format([I_dtmInvoice],”mmm”) In (“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”);

Any idea?

TIA,

Ken

Reply | Quote

Replies

I’m not sure what is causing your problem, but have you tried including that field in your SELECT statement? One other possibility is some sort of missing reference that causes the prompt when you try to lash it to a report.

Reply | Quote

I’m not sure what is causing your problem, but have you tried including that field in your SELECT statement? One other possibility is some sort of missing reference that causes the prompt when you try to lash it to a report.

Reply | Quote

Try changing the PIVOT line to include the table name prior to I_dtmInvoice, ie. [tblOther].[I_dtmInvoice]

Reply | Quote

Try changing the PIVOT line to include the table name prior to I_dtmInvoice, ie. [tblOther].[I_dtmInvoice]

Reply | Quote

If tblOther.dtmInvoice is not a typo in your post, the problem doesn’t seem to be related to the query, since there is no mention of dtmInvoice (as opposed to l_dtmInvoice). I would check if there is anything on the report or in its properties that refers to tblOther.dtmInvoice.

Reply | Quote

Found it! WHEW!
Yes, that was a typo, but the real issue was in the properties window of the report where “Order by” had the tblOther.dtmInvoice inserted. I removed it and viola’ works as expected.

Thanks again for your help.

Ken

Reply | Quote

Found it! WHEW!
Yes, that was a typo, but the real issue was in the properties window of the report where “Order by” had the tblOther.dtmInvoice inserted. I removed it and viola’ works as expected.

Thanks again for your help.

Ken

Reply | Quote