Crosstab query with parameter

[Alan]

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

 

[KARL DEWEY]

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

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

--
KARL DEWEY
Build a little - Test a little

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

 

[Alan]

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

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

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

--
KARL DEWEY
Build a little - Test a little

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

 

[Alan]

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

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

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

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

--
KARL DEWEY
Build a little - Test a little

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

 

[KARL DEWEY]

Try this --
PARAMETERS [Please enter year] TEXT (255);

--
KARL DEWEY
Build a little - Test a little

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

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

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[Alan]

Nope, it's still giving me the same message.

Try this --
PARAMETERS [Please enter year] TEXT (255);

--
KARL DEWEY
Build a little - Test a little

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

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

:

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[KARL DEWEY]

The only suggestion I have is to make a copy and remove much of the joins
WHERE statement to try and make it run. Then add back in until you find the
problem.
--
KARL DEWEY
Build a little - Test a little

Nope, it's still giving me the same message.

Try this --
PARAMETERS [Please enter year] TEXT (255);

--
KARL DEWEY
Build a little - Test a little

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

:

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

:

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[Alan]

Ok. I'll give that a try.

Thanks for your help and advice.

Alan

The only suggestion I have is to make a copy and remove much of the joins
WHERE statement to try and make it run. Then add back in until you find the
problem.
--
KARL DEWEY
Build a little - Test a little

Nope, it's still giving me the same message.

Try this --
PARAMETERS [Please enter year] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


:

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

:

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

:

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[Gary Walter]

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

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

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

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[Gary Walter]

Hi Alan,

More ramblings...probably of little help...

I don't know why you introduce an ORDER BY
in crosstab for a report. I'd just delete the ORDER BY
and let your report sort results. Crosstabs are
"internally demanding beasts" without adding
an ORDER BY that will be ignored by the
report anyway.

Another *very minor* thing you might look at would
be the values themselves of PkgStatus...

They may be perfectly fine text, but not
obey rules for an acceptable field name...
the crosstab has to turn them into field names.

{an old kb but I think should still be valid}

http://support.microsoft.com/kb/94192
ACC: Incorrect Characters in Crosstab Query Column Headings

I doubt that's your problem, but if "IN (...) does not work...

Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?

Sorry for ramblings, but as Karl has sagely advised,
your error message is typically associated with
the declaration of a parameter (which you believe
is solid), so it must be "something" in the difference
between stand-alone query and report recordsource.
(duh! gary).

good luck,

gary

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

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;

 

[Gary Walter]

One other "diagnostic test" might be
to make a table from your crosstab.
Then design a test report based on
that table to see if data is somehow
playing a part in your problem.

More ramblings...probably of little help...

I don't know why you introduce an ORDER BY
in crosstab for a report. I'd just delete the ORDER BY
and let your report sort results. Crosstabs are
"internally demanding beasts" without adding
an ORDER BY that will be ignored by the
report anyway.

Another *very minor* thing you might look at would
be the values themselves of PkgStatus...

They may be perfectly fine text, but not
obey rules for an acceptable field name...
the crosstab has to turn them into field names.

{an old kb but I think should still be valid}

http://support.microsoft.com/kb/94192
ACC: Incorrect Characters in Crosstab Query Column Headings

I doubt that's your problem, but if "IN (...) does not work...

Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?

Sorry for ramblings, but as Karl has sagely advised,
your error message is typically associated with
the declaration of a parameter (which you believe
is solid), so it must be "something" in the difference
between stand-alone query and report recordsource.
(duh! gary).

good luck,

gary

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

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;

 

[Alan]

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

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

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

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

:

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

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

--
KARL DEWEY
Build a little - Test a little


:

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

 

[Alan]

The ORDER BY in the cross tab was just a carry over from the query when I was
testing data. I've removed it to reduce confusion.

"Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?"

Nope. The query looks clean.

I'll poke around some more in the areas you suggested. Thanks for the info.

Alan

Hi Alan,

More ramblings...probably of little help...

I don't know why you introduce an ORDER BY
in crosstab for a report. I'd just delete the ORDER BY
and let your report sort results. Crosstabs are
"internally demanding beasts" without adding
an ORDER BY that will be ignored by the
report anyway.

Another *very minor* thing you might look at would
be the values themselves of PkgStatus...

They may be perfectly fine text, but not
obey rules for an acceptable field name...
the crosstab has to turn them into field names.

{an old kb but I think should still be valid}

http://support.microsoft.com/kb/94192
ACC: Incorrect Characters in Crosstab Query Column Headings

I doubt that's your problem, but if "IN (...) does not work...

Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?

Sorry for ramblings, but as Karl has sagely advised,
your error message is typically associated with
the declaration of a parameter (which you believe
is solid), so it must be "something" in the difference
between stand-alone query and report recordsource.
(duh! gary).

good luck,

gary

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

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;

 

[rosieb]

The ORDER BY in the cross tab was just a carry over from the query when I was
testing data. I've removed it to reduce confusion.

"Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?"

Nope. The query looks clean.

I'll poke around some more in the areas you suggested. Thanks for the info..

Alan

Hi Alan,

More ramblings...probably of little help...

I don't know why you introduce an ORDER BY
in crosstab for a report. I'd just delete the ORDER BY
and let your report sort results. Crosstabs are
"internally demanding beasts" without adding
an ORDER BY that will be ignored by the
report anyway.

Another *very minor* thing you might look at would
be the values themselves of PkgStatus...

They may be perfectly fine text, butnot
obey rules for an acceptable field name...
the crosstab has to turn them into field names.

{an old kb but I think should still be valid}

http://support.microsoft.com/kb/94192
ACC: Incorrect Characters in Crosstab Query Column Headings

I doubt that's your problem, but if "IN (...)doesnotwork...

Also, when you look at the working query,
do you get a "<>" column, i.e., somehow in
your joins you end up with a NULL pivot?

Sorry for ramblings, but as Karl has sagely advised,
your error message is typically associated with
the declaration of a parameter (which you believe
is solid), so it must be "something" in the difference
between stand-alone query and report recordsource.
(duh! gary).

good luck,

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- Hide quoted text -

- Show quoted text -

I've just hit the same problem, adding all of the possible column
heading values into the Column Headings property of the crosstab query
seems to have fixed it.

Rosie