Dynamic Crosstab Problem

[Claudette Hennessy]

I have a report based on a crosstab query. I wish to limit the data to a
specific year, which is selected from a combo box on a form.
Crosstab: qxtCurrentDepositChecklist

TRANSFORM First(qryCurrentDepositChecklist.Deposit) AS FirstOfDeposit
SELECT qryCurrentDepositChecklist.ShopFirstName,
qryCurrentDepositChecklist.ShopName, qryCurrentDepositChecklist.Year,
First(qryCurrentDepositChecklist.Deposit) AS [Total Of Deposit]
FROM qryCurrentDepositChecklist
GROUP BY qryCurrentDepositChecklist.ShopFirstName,
qryCurrentDepositChecklist.ShopName, qryCurrentDepositChecklist.Year
PIVOT qryCurrentDepositChecklist.ShowName;

The source query: qryCurrentDepositChecklist

SELECT DISTINCT tblDealer.ShopFirstName, tblDealer.ShopName,
sqryCurrentDepositChecklist.ShowName,

sqryCurrentDepositChecklist.Year, tblEvent.Deposit
FROM (sqryCurrentDepositChecklist INNER JOIN tblDealer ON
sqryCurrentDepositChecklist.ShopID = tblDealer.ShopID) INNER

JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID
WHERE (((sqryCurrentDepositChecklist.Year)=[Forms]![frmDialogDot]![cboYear])
AND ((tblEvent.Deposit)>0));

I get an error message "Microsoft Jet Engine does not recognize
Forms!frmDialogDot!cboYear as a valid fieldname or expression"

The crosstab query does not permit Forms]![frmDialogDot]![cboYear] as a
criterion for the Year field. How do I limit the data based on the year of
choice?

Thank you in advance
Claudette

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

You should end up with assuming that Year is a number field.

PARAMETERS [Forms]![frmDialogDot]![cboYear] Long;
SELECT DISTINCT tblDealer.ShopFirstName, tblDealer.ShopName,
sqryCurrentDepositChecklist.ShowName,

sqryCurrentDepositChecklist.Year, tblEvent.Deposit
FROM (sqryCurrentDepositChecklist INNER JOIN tblDealer ON
sqryCurrentDepositChecklist.ShopID = tblDealer.ShopID) INNER

JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID
WHERE (((sqryCurrentDepositChecklist.Year)=[Forms]![frmDialogDot]![cboYear])
AND ((tblEvent.Deposit)>0));

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Claudette Hennessy]

Thank you, thank you. It works fine now. At one point I had tried defining
the parameter, but I didn't put the full path name in.
Claudette

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

You should end up with assuming that Year is a number field.

PARAMETERS [Forms]![frmDialogDot]![cboYear] Long;
SELECT DISTINCT tblDealer.ShopFirstName, tblDealer.ShopName,
sqryCurrentDepositChecklist.ShowName,

sqryCurrentDepositChecklist.Year, tblEvent.Deposit
FROM (sqryCurrentDepositChecklist INNER JOIN tblDealer ON
sqryCurrentDepositChecklist.ShopID = tblDealer.ShopID) INNER

JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID
WHERE
(((sqryCurrentDepositChecklist.Year)=[Forms]![frmDialogDot]![cboYear])
AND ((tblEvent.Deposit)>0));

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a report based on a crosstab query. I wish to limit the data to a
specific year, which is selected from a combo box on a form.
Crosstab: qxtCurrentDepositChecklist

TRANSFORM First(qryCurrentDepositChecklist.Deposit) AS FirstOfDeposit
SELECT qryCurrentDepositChecklist.ShopFirstName,
qryCurrentDepositChecklist.ShopName, qryCurrentDepositChecklist.Year,
First(qryCurrentDepositChecklist.Deposit) AS [Total Of Deposit]
FROM qryCurrentDepositChecklist
GROUP BY qryCurrentDepositChecklist.ShopFirstName,
qryCurrentDepositChecklist.ShopName, qryCurrentDepositChecklist.Year
PIVOT qryCurrentDepositChecklist.ShowName;

The source query: qryCurrentDepositChecklist

SELECT DISTINCT tblDealer.ShopFirstName, tblDealer.ShopName,
sqryCurrentDepositChecklist.ShowName,

sqryCurrentDepositChecklist.Year, tblEvent.Deposit
FROM (sqryCurrentDepositChecklist INNER JOIN tblDealer ON
sqryCurrentDepositChecklist.ShopID = tblDealer.ShopID) INNER

JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID
WHERE
(((sqryCurrentDepositChecklist.Year)=[Forms]![frmDialogDot]![cboYear])
AND ((tblEvent.Deposit)>0));

I get an error message "Microsoft Jet Engine does not recognize
Forms!frmDialogDot!cboYear as a valid fieldname or expression"

The crosstab query does not permit Forms]![frmDialogDot]![cboYear] as a
criterion for the Year field. How do I limit the data based on the year
of choice?

Thank you in advance
Claudette