Re: Crosstab query with parameter

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Gary,

Thanks for all the info and tips.

The package statuses should be constant. I have it so that even if a
particular package status has no data, it still displays zeros in the column
(see below snippet of sample data) and the report picks them all up so the
columns are always being created. The only things that could vary are new
salesmen being added to the database, year and the markets they quote in as
that has the potential to change all the time but those are row headings that
the report doesn't have to create static fields for.

QYY Salesman Market Awarded Budgetary Cancelled Lost
2007 Terry C / I $7,430,073.15 $0.00 $0.00 $0.00
2007 Terry OEM $345,215.56 $0.00 $0.00 $0.00
2007 Terry Oil & Gas $3,074,364.45 $0.00 $0.00 $0.00

The date field is automatically filled in but it is possible for the
salesman field and the market field to be blank if they forget to fill it in
however that shouldn't have a bearing on the report running should it if
those are row heading fields??

Alan

"Gary Walter" wrote:

Hi Alan,

PMFJI

I may be wrong, but you have a crosstab that works
"with the parameter in query mode but not when
it's attached to a report"

I cannot imagine sending a crosstab query to
a report without explicitly defining the column headings
so those fields will *always exist* for your report.

Are all the PkgStatus values constant?

PIVOT tblQuotePkgStatusList.PkgStatus
IN
("OrderProcessed", "SentToShipper", "etc")

Those columns that the crosstab creates via
PIVOT "out of the plain air" have to be defined
consistently somehow so your report can bind
to them no matter what the data.

I might investigate that as a possible reason
to your report error.

of course I could be wrong...

gary


"Alan" wrote:
Maybe this will help:

PARAMETERS [Please enter year] Short;
TRANSFORM Sum(qryQuotePkgAmt.[Total Price]) AS [Market Total]
SELECT Format([quoteDate],"yyyy") AS QYY, qryStaffList.Salesman,
tblQuoteHeader.market
FROM (tblQuotePkgStatusList INNER JOIN (tblQuoteHeader INNER JOIN
qryQuotePkgAmt ON tblQuoteHeader.quoteID = qryQuotePkgAmt.quoteID) ON
tblQuotePkgStatusList.ID = qryQuotePkgAmt.pkgStatusID) INNER JOIN
qryStaffList ON tblQuoteHeader.salesman = qryStaffList.empNo
WHERE (((Format([quoteDate],"yyyy"))=[Please enter year]))
GROUP BY Format([quoteDate],"yyyy"), qryStaffList.Salesman,
tblQuoteHeader.market
ORDER BY qryStaffList.Salesman
PIVOT tblQuotePkgStatusList.PkgStatus;


Any help would be appreciated as this has me stumped.

Thanks,

Alan

"Alan" wrote:

Thanks for the reply.

I changed the format to what's listed below and get the same error
message.
It works in query mode fine though.

The problem is that I can't tell what field it doesn't like as all the
message displays is the quotation mark where a field name normally
appears.


Alan

"KARL DEWEY" wrote:

If your "quoteDate" field is datetime field then try ---

QYY: Format([quoteDate],"yyyy")

--
KARL DEWEY
Build a little - Test a little


"Alan" wrote:

Hello,

I have a crosstab query that has a parameter that prompts the user to
enter
the year of the data he/she wishes to see. The parameter is declared
in the
Parameter properties window as an integer and the query runs
flawlessly in
query mode. The moment this query is attached to a report, it throws
an error
indicating that "The Microsoft Jet database engine does not recognize
" as a
valid field name or expression."

The year is extracted from the "quoteDate" field as

QYY: Format(right([quoteDate],4))

Why does this crosstab work with the parameter in query mode but not
when
it's attached to a report? I don't really want the report to print
all years
of the data as that's a waste of paper and the user typically will
only be
interested in a specific year.

Thanks,

Alan



.

Relevant Pages

  • RE: Crosstab query with parameter
    ... "Alan" wrote: ... It works in query mode fine though. ... "KARL DEWEY" wrote: ... The moment this query is attached to a report, ...
    (microsoft.public.access.queries)
  • Daring to use QC
    ... Interestingly enough, if I put my cursor over the question mark character at the top, it says "View form in query mode". ... and the 'description' box has changed to 'keywords'. ... let's enter 'multiple selection' as a keyword item... ... It is left with the last report I viewed, which has to do with Quality Central. ...
    (borland.public.delphi.non-technical)
  • Re: Crosstab query with parameter
    ... but you have a crosstab that works ... a report without explicitly defining the column headings ... "Alan" wrote: ... It works in query mode fine though. ...
    (microsoft.public.access.queries)
  • RE: Crosstab query with parameter
    ... "KARL DEWEY" wrote: ... "Alan" wrote: ... It works in query mode fine though. ... The moment this query is attached to a report, ...
    (microsoft.public.access.queries)
  • RE: Crosstab query with parameter
    ... "Alan" wrote: ... "KARL DEWEY" wrote: ... It works in query mode fine though. ... The moment this query is attached to a report, ...
    (microsoft.public.access.queries)