Too few parameters error with a IIF statement

[Marcus]

I'm trying to do a Pivot Table in Excel with 'external data source'
and in this case the data is stored in a Access db.

In Excel I get this error message, "Too few parameters. Expected 2."
And as I understand, I get this error when in cases where I a
parameter query in the Access db.

In this case, the issue is this line. When I remove this line the
import works fine. And when I run the query in Access, I don't have
too set any parameter or get any error.

iif ([Belopp] >= 0, "Plus", "Minus") AS Post

Does any one know why Excel thinks it's a parameter query and how I
solve it?

 

[John W. Vinson]

I'm trying to do a Pivot Table in Excel with 'external data source'
and in this case the data is stored in a Access db.

In Excel I get this error message, "Too few parameters. Expected 2."
And as I understand, I get this error when in cases where I a
parameter query in the Access db.

In this case, the issue is this line. When I remove this line the
import works fine. And when I run the query in Access, I don't have
too set any parameter or get any error.

iif ([Belopp] >= 0, "Plus", "Minus") AS Post

Does any one know why Excel thinks it's a parameter query and how I
solve it?

Please post more of the context. What you posted isn't a query, just a snippet
of one; it's hard to even guess what might be at fault.

In fact, is this from the SQL of a Query, or from VBA code in an Excel Macro?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Phil Hunt]

Doing it with ODBC ?
Access and Excel automation is fraught with many un-explainanle bugs.
At 1 time, I have to change the field name to make things work.

I'm trying to do a Pivot Table in Excel with 'external data source'
and in this case the data is stored in a Access db.

In Excel I get this error message, "Too few parameters. Expected 2."
And as I understand, I get this error when in cases where I a
parameter query in the Access db.

In this case, the issue is this line. When I remove this line the
import works fine. And when I run the query in Access, I don't have
too set any parameter or get any error.

iif ([Belopp] >= 0, "Plus", "Minus") AS Post

Does any one know why Excel thinks it's a parameter query and how I
solve it?

 

[Marcus]

No macro, it's a SQL in Access. Here is the whole SQL

SELECT Direkt.Text, Grupper.Grupp, Referens.Undergrupp, Direkt.Belopp,
Year([Direkt].[Datum]) AS År, Month([Direkt].[Datum]) AS Månad, iif
([Belopp] >= 0, "Plus", "Minus") AS Post
FROM (Grupper RIGHT JOIN Referens ON Grupper.Undergrupp =
Referens.Undergrupp) RIGHT JOIN Direkt ON Referens.Text = Direkt.Text;
UNION SELECT Faktura.Text, Grupper.Grupp, Referens.Undergrupp,
Faktura.Belopp, Year([Faktura].[Datum]) AS År, Month([Faktura].
[Datum]) AS Månad, iif ([Belopp] >= 0, "Plus", "Minus") AS Post
FROM (Grupper RIGHT JOIN Referens ON Grupper.Undergrupp =
Referens.Undergrupp) RIGHT JOIN Faktura ON Referens.Text =
Faktura.Text;

I'm using Office 2010 and I use the 'PivotTable Wizard' from Excel
choosing 'External data source' and the 'MS Access Database' option,
I'm not sure but I think it's the ODBC connection.

 

[Marcus]

I found a flaw in my SQL, I forget to point of the table name for the
field 'Belopp', a more correct SQL below.
But that didin't solve the problem, I have notice it's the combination
of the UNION and the forumulas. If I remove the second SQL, the import
works fine. If I remove the formula, the import works fine.

SELECT Direkt.Text, Grupper.Grupp, Referens.Undergrupp, Direkt.Belopp,
Year([Direkt].[Datum]) AS År, Month([Direkt].[Datum]) AS Månad, iif
([Direkt.Belopp] >= 0, "Plus", "Minus") AS Post
FROM (Grupper RIGHT JOIN Referens ON Grupper.Undergrupp =
Referens.Undergrupp) RIGHT JOIN Direkt ON Referens.Text = Direkt.Text;
UNION SELECT Faktura.Text, Grupper.Grupp, Referens.Undergrupp,
Faktura.Belopp, Year([Faktura].[Datum]) AS År, Month([Faktura].
[Datum]) AS Månad, iif ([Faktura.Belopp] >= 0, "Plus", "Minus") AS
Post
FROM (Grupper RIGHT JOIN Referens ON Grupper.Undergrupp =
Referens.Undergrupp) RIGHT JOIN Faktura ON Referens.Text =
Faktura.Text;

 

[Marcus]

Found the problem, I'm not allowed the use the double quote (")
character in a UNION. I have to use the single (').
I think it's strange that a UNION is sensitive to this. Well, solved
it.

iif([Direkt.Belopp] >= 0, 'Plus', 'Minus') AS Post