Too Few Parameters error in Union query

  • Thread starter Thread starter TK
  • Start date Start date
T

TK

I have a union query that runs fine in Access, but when I
try to use this query as a data source for a pivot table
in Excel it returns an error stating: "Too few parameters.
Expected 2"

Each of the individual SELECT statements that are combined
in the union query work fine when used alone as a data
source in Excel, but the above error occurs when I combine
them in a union query.

There are no parameters in the query, so I'm confused by
the error. Has anyone had a similar problem or know of a
workaround to get a Union query to work as the data source
for an Excel Pivot Table?

Thanks,

TK
 
Depends on if you run it through the query window or through code.

Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Dim prm As Parameter

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError
 
Thanks for your reply.

The problem is that there are no parameters. The query is
a union of two select statements but there are no
parameters, and there is no code involved.

I cannot figure out why or where parameters are expected
since the query does not call for them.
 
TK said:
I have a union query that runs fine in Access, but when I
try to use this query as a data source for a pivot table
in Excel it returns an error stating: "Too few parameters.
Expected 2"

Each of the individual SELECT statements that are combined
in the union query work fine when used alone as a data
source in Excel, but the above error occurs when I combine
them in a union query.

There are no parameters in the query, so I'm confused by
the error. Has anyone had a similar problem or know of a
workaround to get a Union query to work as the data source
for an Excel Pivot Table?
Click to expand...
Hi TK,

I don't spend much time in Excel,
but I found this on Google Groups:

****quote******
From: Martijn Evers ([email protected])
Subject: Re: MS Query and SQL
Newsgroups: microsoft.public.excel.programming
Date: 2001-02-21 05:38:04 PST


By setting up the query as an pass-through query

in MS Query go to File->Execute SQL and paste/type your statement, MS query
should not complain, but will let the database source(Access) run the query
directly

--
Regards

Martijn Evers AKA Dm Unseen
Do Bee said:
Hi All:

The Pivot Table Wizard in Excel uses MS Query to pull data from a Union
query in Access. As a part of the Union query I have something like this:

SELECT All "All Names" as [NameField] .... From tblSampleTable
Union Select All [NameField] ..... From tbleSampleTwoTable

The idea is that for this table, I want "All Names to appear in the
NameField field. For the second table, the actual value of the NameField
will appear. If I run the Union query in Access, the results are as I
expect. However, as I'm setting up the pivot table to run this query, MS
Query says that a parameter is missing. How can I set up the SQL to keep MS
Query from balking at this section of my SQL and still give me the desired
results?

tod
Click to expand...
 
Of course..in tod's case, he probably was getting
parameter error because the SQL had "All" in each
clause and Access did not recognise it, so thought they
were parameters, i.e., it should have been

SELECT "All Names" as [NameField] ,.... From tblSampleTable
Union All
Select [NameField], ..... From tbleSampleTwoTable



Gary Walter said:
TK said:
I have a union query that runs fine in Access, but when I
try to use this query as a data source for a pivot table
in Excel it returns an error stating: "Too few parameters.
Expected 2"

Each of the individual SELECT statements that are combined
in the union query work fine when used alone as a data
source in Excel, but the above error occurs when I combine
them in a union query.

There are no parameters in the query, so I'm confused by
the error. Has anyone had a similar problem or know of a
workaround to get a Union query to work as the data source
for an Excel Pivot Table?
Click to expand...
Hi TK,

I don't spend much time in Excel,
but I found this on Google Groups:

****quote******
From: Martijn Evers ([email protected])
Subject: Re: MS Query and SQL
Newsgroups: microsoft.public.excel.programming
Date: 2001-02-21 05:38:04 PST


By setting up the query as an pass-through query

in MS Query go to File->Execute SQL and paste/type your statement, MS query
should not complain, but will let the database source(Access) run the query
directly

--
Regards

Martijn Evers AKA Dm Unseen
Do Bee said:
Hi All:

The Pivot Table Wizard in Excel uses MS Query to pull data from a Union
query in Access. As a part of the Union query I have something like this:

SELECT All "All Names" as [NameField] .... From tblSampleTable
Union Select All [NameField] ..... From tbleSampleTwoTable

The idea is that for this table, I want "All Names to appear in the
NameField field. For the second table, the actual value of the NameField
will appear. If I run the Union query in Access, the results are as I
expect. However, as I'm setting up the pivot table to run this query, MS
Query says that a parameter is missing. How can I set up the SQL to keep MS
Query from balking at this section of my SQL and still give me the desired
results?

tod
Click to expand...
Click to expand...
 
Thanks Gary,

That helps. It seems that the problem was coming from a
literal string value that was included in the GROUP BY
clause, (Access generated SQL). This seems to be the
problem since it all works when I cleaned up the Group BY
clause.

Thanks,

TK

-----Original Message-----

TK said:
I have a union query that runs fine in Access, but when I
try to use this query as a data source for a pivot table
in Excel it returns an error stating: "Too few parameters.
Expected 2"

Each of the individual SELECT statements that are combined
in the union query work fine when used alone as a data
source in Excel, but the above error occurs when I combine
them in a union query.

There are no parameters in the query, so I'm confused by
the error. Has anyone had a similar problem or know of a
workaround to get a Union query to work as the data source
for an Excel Pivot Table?
Click to expand...
Hi TK,

I don't spend much time in Excel,
but I found this on Google Groups:

****quote******
From: Martijn Evers ([email protected])
Subject: Re: MS Query and SQL
Newsgroups: microsoft.public.excel.programming
Date: 2001-02-21 05:38:04 PST


By setting up the query as an pass-through query

in MS Query go to File->Execute SQL and paste/type your statement, MS query
should not complain, but will let the database source (Access) run the query
directly

--
Regards

Martijn Evers AKA Dm Unseen
Do Bee said:
Hi All:

The Pivot Table Wizard in Excel uses MS Query to pull data from a Union
query in Access. As a part of the Union query I have something like this:

SELECT All "All Names" as [NameField] .... From tblSampleTable
Union Select All [NameField] ..... From tbleSampleTwoTable

The idea is that for this table, I want "All Names to appear in the
NameField field. For the second table, the actual value of the NameField
will appear. If I run the Union query in Access, the results are as I
expect. However, as I'm setting up the pivot table to run this query, MS
Query says that a parameter is missing. How can I set up the SQL to keep MS
Query from balking at this section of my SQL and still give me the desired
results?

tod
Click to expand...


.
Click to expand...
 
Back
Top