Parameters are not allowed in queries that can't be displayed grap

[Guest]

Until now, I have always used MSQuery to create queries with parameters, and
then embedded the query into an Excel spreadsheet. This would allow me to
anchor my parameters to certain cells on the spreadsheet. My report users
could then change the values in those cells and click a button to refresh the
query data. This works wonderfully with simple queries.

Now I have created a very complex query, specifically a query that uses a
subquery as its SELECT FROM. When I try to set up my parameters in MSQuery
for this query... it says "parameters are not allowed in queries that can't
be displayed graphically." Without being able to put parameters in my query,
I can't automate this using Excel.

Is there some other way to accomplish what I want to do?

Thanks,

Flora

My SQL is:

SELECT UM00163T.METER_NO, UM00100M.LOCATION_ID_CUSTOM, UM00163T.READ_DTM,
UM00163T.READ_AMT
FROM UM00100M, UM00163T,
(SELECT UM00163T.METER_NO, MAX(UM00163T.READ_DTM) AS READ_DTM
FROM UM00163T
GROUP BY UM00163T.METER_NO)TEMP
WHERE UM00100M.LOCATION_NO = UM00163T.LOCATION_NO AND UM00163T.CYCLE_CD=?
AND UM00163T.METER_NO = TEMP.METER_NO AND
UM00163T.READ_DTM = TEMP.READ_DTM
ORDER BY UM00100M.LOCATION_ID_CUSTOM

 

[Jamie Collins]

Until now, I have always used MSQuery to create queries with parameters, and
then embedded the query into an Excel spreadsheet. This would allow me to
anchor my parameters to certain cells on the spreadsheet.

Now I have created a very complex query, specifically a query that uses a
subquery as its SELECT FROM. When I try to set up my parameters in MSQuery
for this query... it says "parameters are not allowed in queries that can't
be displayed graphically."

Create a VIEW on the database server containing the SQL. Modify your
Excel app to query the VIEW rather than the base tables.

Jamie.

--