Invalid Parameters

[Martin]

Hello,

I have a query that is a cross tab and is based on a form drop down box.
This works fine from the frop down box but then I added the "(All)" option
using a union select in the drop down box and now the query says invalid
bracketing. I have gone into the SQL view and played around with this but
cant get it to work. Here is the SQL:

PARAMETERS [IIf([Forms]![Main
Menu]![mtord]='(All)',[text1]![mtord],[Forms]![Main Menu]![mtord])] Value,
[Forms]![Main Menu]![salary] Value;
TRANSFORM Sum(text1!mtincome)/Sum(text1!ncust) AS Expr1
SELECT text1.mtord, text1.salary, "mtincome" AS Category
FROM text1
WHERE (((text1.mtord)=IIf([Forms]![Main
Menu]![mtord]='(All)',[text1]![mtord],[Forms]![Main Menu]![mtord])) AND
((text1.salary)=[Forms]![Main Menu]![salary]))
GROUP BY text1.mtord, text1.salary
PIVOT text1.deccont;


Can anyone help me please?

Thanks in advance,

Martin

 

[John Spencer]

Your parameter clause is faulty. It should look more like the following:

PARAMETERS [Forms]![Main Menu]![mtord] Text(255),
[Forms]![Main Menu]![salary] Currency;
TRANSFORM Sum(text1!mtincome)/Sum(text1!ncust) AS Expr1
SELECT text1.mtord, text1.salary, "mtincome" AS Category
FROM text1
WHERE text1.mtord =IIf([Forms]![Main Menu]![mtord]='(All)',
[text1].[mtord],[Forms]![Main Menu]![mtord]) AND
text1.salary)=[Forms]![Main Menu]![salary]
GROUP BY text1.mtord, text1.salary
PIVOT text1.deccont;

Also you COULD change the where clause to
WHERE text1.mtord LIKE IIf([Forms]![Main Menu]![mtord]='(All)',
"*",[Forms]![Main Menu]![mtord])
AND text1.salary)=[Forms]![Main Menu]![salary]
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Martin]

John, thank you, this fixed the problem

Your parameter clause is faulty. It should look more like the following:

PARAMETERS [Forms]![Main Menu]![mtord] Text(255),
[Forms]![Main Menu]![salary] Currency;
TRANSFORM Sum(text1!mtincome)/Sum(text1!ncust) AS Expr1
SELECT text1.mtord, text1.salary, "mtincome" AS Category
FROM text1
WHERE text1.mtord =IIf([Forms]![Main Menu]![mtord]='(All)',
[text1].[mtord],[Forms]![Main Menu]![mtord]) AND
text1.salary)=[Forms]![Main Menu]![salary]
GROUP BY text1.mtord, text1.salary
PIVOT text1.deccont;

Also you COULD change the where clause to
WHERE text1.mtord LIKE IIf([Forms]![Main Menu]![mtord]='(All)',
"*",[Forms]![Main Menu]![mtord])
AND text1.salary)=[Forms]![Main Menu]![salary]
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,

I have a query that is a cross tab and is based on a form drop down box.
This works fine from the frop down box but then I added the "(All)" option
using a union select in the drop down box and now the query says invalid
bracketing. I have gone into the SQL view and played around with this but
cant get it to work. Here is the SQL:

PARAMETERS [IIf([Forms]![Main
Menu]![mtord]='(All)',[text1]![mtord],[Forms]![Main Menu]![mtord])] Value,
[Forms]![Main Menu]![salary] Value;
TRANSFORM Sum(text1!mtincome)/Sum(text1!ncust) AS Expr1
SELECT text1.mtord, text1.salary, "mtincome" AS Category
FROM text1
WHERE (((text1.mtord)=IIf([Forms]![Main
Menu]![mtord]='(All)',[text1]![mtord],[Forms]![Main Menu]![mtord])) AND
((text1.salary)=[Forms]![Main Menu]![salary]))
GROUP BY text1.mtord, text1.salary
PIVOT text1.deccont;


Can anyone help me please?

Thanks in advance,

Martin