IIF in crosstab

[Martin]

Hello,

I have read up on the paramters section in cross tab queries and got this far:

PARAMETERS [IIf([Forms]![Main
Menu]![mtord]="(All)",[text1]![mtord],[Forms]![Main Menu]![mtord])] Text (
255 );
TRANSFORM Sum([text1]![mtincome])/Sum([text1]![ncust]) AS Expr1
SELECT text1.mtord, text1.mttype, text1.accname, text1.GENDER, text1.ageg,
text1.segmentg, text1.pref1, text1.mtgr, text1.mtopen, text1.salary,
"mtincome" AS Category
FROM text1
GROUP BY text1.mtord, text1.mttype, text1.accname, text1.GENDER, text1.ageg,
text1.segmentg, text1.pref1, text1.mtgr, text1.mtopen, text1.salary,
"mtincome"
PIVOT text1.deccont;


There is mention of removing the [] from the IIF statement in the SQL view
but when I do this I get an invalid bracketing error. Can anyone help, I
think the problem is in the first line of the SQL.

Thanks in advance,

Martin

 

[John Spencer]

PERHAPS what you want is the following.

PARAMETERS [Forms]![Main Menu]![mtord] Text (255);
TRANSFORM Sum([text1]![mtincome])/Sum([text1]![ncust]) AS Expr1
SELECT text1.mtord, text1.mttype, text1.accname, text1.GENDER, text1.ageg,
text1.segmentg, text1.pref1, text1.mtgr, text1.mtopen, text1.salary,
"mtincome" AS Category
FROM text1
WHERE text1.mTord = [Forms]![Main Menu]![mtord] OR
[Forms]![Main Menu]![mtord]="ALL"
GROUP BY text1.mtord, text1.mttype, text1.accname, text1.GENDER,
text1.ageg,
text1.segmentg, text1.pref1, text1.mtgr, text1.mtopen, text1.salary,
"mtincome"
PIVOT text1.deccont;



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