Dynamic Fields in Crosstab

[Claudette Hennessy]

Recently Karl Dewey posted a solution to dynamic fields to add In
("Spring","Summer","Fall") to the PIVOT line.

PARAMETERS [Forms]![frmDialogContractMailMerge]![cboYear] Short;
TRANSFORM First(tblContract.SendContract) AS FirstOfSendContract
SELECT tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
FROM tblContract
WHERE (((Get_ShowName([ShowID])) In ("Spring","Summer","Fall")) AND
((tblContract.ShowYear)=[Forms]![frmDialogContractMailMerge]![cboYear]))
GROUP BY tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
PIVOT Get_ShowName([ShowID]);

I get syntax complaints combining the In clause with the function.

I need the result:

ShopID ShowYear Spring Summer Fall
1 2009 Y
2 2009 Y
.....

How can I force the crosstab to list the other fields which are presently
not populated?
this query is joined to another which fails unless the fields "Summer" and
"Fall" are present in the crosstab.:

SELECT tblDealer.ShopID, tblDealer.ShopName, qxttbllContract.Spring,
qxttbllContract.Summer, qxttbllContract.Fall, qxttbllContract.DateMailed,
qxttbllContract.ShowYear
FROM tblDealer LEFT JOIN qxttbllContract ON tblDealer.ShopID =
qxttbllContract.ShopID;

thank you in advance,
Claudette

 

[KARL DEWEY]

Try changing to this ---
PIVOT Get_ShowName([ShowID]) In ("Spring","Summer","Fall");

What is Get_ShowName([ShowID]) - a table and field? That syntax is new
to me.

 

[Claudette Hennessy]

That works!!, Thank you! Get_ShowName is a function, I pass it the
ShowID, I get the ShowName. I am never sure if I need the []'s or not.
Sometimes the compiler puts them in if I don't.

Claudette

Try changing to this ---
PIVOT Get_ShowName([ShowID]) In ("Spring","Summer","Fall");

What is Get_ShowName([ShowID]) - a table and field? That syntax is
new
to me.

--
KARL DEWEY
Build a little - Test a little

Recently Karl Dewey posted a solution to dynamic fields to add In
("Spring","Summer","Fall") to the PIVOT line.

PARAMETERS [Forms]![frmDialogContractMailMerge]![cboYear] Short;
TRANSFORM First(tblContract.SendContract) AS FirstOfSendContract
SELECT tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
FROM tblContract
WHERE (((Get_ShowName([ShowID])) In ("Spring","Summer","Fall")) AND
((tblContract.ShowYear)=[Forms]![frmDialogContractMailMerge]![cboYear]))
GROUP BY tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
PIVOT Get_ShowName([ShowID]);

I get syntax complaints combining the In clause with the function.

I need the result:

ShopID ShowYear Spring Summer Fall
1 2009 Y
2 2009 Y
.....

How can I force the crosstab to list the other fields which are presently
not populated?
this query is joined to another which fails unless the fields "Summer"
and
"Fall" are present in the crosstab.:

SELECT tblDealer.ShopID, tblDealer.ShopName, qxttbllContract.Spring,
qxttbllContract.Summer, qxttbllContract.Fall, qxttbllContract.DateMailed,
qxttbllContract.ShowYear
FROM tblDealer LEFT JOIN qxttbllContract ON tblDealer.ShopID =
qxttbllContract.ShopID;

thank you in advance,
Claudette