The following applies to Excel/MSQuery 2000 and 2003. Not tested yet on 2007.

If a query through Excel (via MSQuery) attempts to use a parameter when a
GROUP BY is required, the query uses a HAVING clause (even though it may not
be required) and is submitted to the ODBC connection without prompting the
user for the variable thus failing to execute.

The fail point is on the HAVING clause. I can run queries with GROUP BY,
changing the HAVING to a WHERE as long as the filter is not on the SUM (or
any aggregation).

Now HAVING should only be used on SUM (aggregations) as this is a very
inefficient filter, but even so, it does not work. As far as I can see,
there is no way to get a filter on a SUM to work properly via an ODBC
connection to a non-Microsoft database.

Cheers,
Stephanie.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

UPDATE: This also fails using a Microsoft database (used Access).

Hey Microsoft guys, if there is a work-around, please let me know.
Otherwise the utility of using Excel is greatly diminished on this project I
am doing.


"Stephanie" wrote:

The following applies to Excel/MSQuery 2000 and 2003. Not tested yet on 2007.

If a query through Excel (via MSQuery) attempts to use a parameter when a
GROUP BY is required, the query uses a HAVING clause (even though it may not
be required) and is submitted to the ODBC connection without prompting the
user for the variable thus failing to execute.

The fail point is on the HAVING clause. I can run queries with GROUP BY,
changing the HAVING to a WHERE as long as the filter is not on the SUM (or
any aggregation).

Now HAVING should only be used on SUM (aggregations) as this is a very
inefficient filter, but even so, it does not work. As far as I can see,
there is no way to get a filter on a SUM to work properly via an ODBC
connection to a non-Microsoft database.

Cheers,
Stephanie.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc