"Enter Parameter Value" Problem

[kjloh]

Dear all,

I have a rather straight-forward query as shown below. Access 2003 keeps asking "Enter Parameter Value" for tot which is sum(Sheet1.Notional). Any idea what's wrong with this query? Thanks.

SELECT Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type], sum(Sheet1.Notional) AS tot
FROM Sheet1
WHERE Sheet1.STRUCTURE in (SELECT Sheet1.STRUCTURE from Sheet1 where trim(Sheet1.STRUCTURE) <> "" and TRIM(Sheet1.[Product Type]) = "Bond") and (tot <> 0)
GROUP BY Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type]
ORDER BY Sheet1.STRUCTURE, Sheet1.Portfolio;

 

[jal]

Dear all,

I have a rather straight-forward query as shown below. Access 2003 keeps asking "Enter Parameter Value" for tot which is sum(Sheet1.Notional). Any idea what's wrong with this query? Thanks.

SELECT Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type], sum(Sheet1.Notional) AS tot
FROM Sheet1
WHERE Sheet1.STRUCTURE in (SELECT Sheet1.STRUCTURE from Sheet1 where trim(Sheet1.STRUCTURE) <> "" and TRIM(Sheet1.[Product Type]) = "Bond") and (tot <> 0)
GROUP BY Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type]
ORDER BY Sheet1.STRUCTURE, Sheet1.Portfolio;

I am probably missing something obvious, but I don't get the logic of your query. "tot" is an aggregate field. Are you allowed to have aggregate fields in a WHERE clause? (I've never tried it).

 

[jal]

Dear all,

I have a rather straight-forward query as shown below. Access 2003 keeps asking "Enter Parameter Value" for tot which is sum(Sheet1.Notional). Any idea what's wrong with this query? Thanks.

SELECT Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type], sum(Sheet1.Notional) AS tot
FROM Sheet1
WHERE Sheet1.STRUCTURE in (SELECT Sheet1.STRUCTURE from Sheet1 where trim(Sheet1.STRUCTURE) <> "" and TRIM(Sheet1.[Product Type]) = "Bond") and (tot <> 0)
GROUP BY Sheet1.STRUCTURE, Sheet1.Portfolio, Sheet1.[Product Type]
ORDER BY Sheet1.STRUCTURE, Sheet1.Portfolio;

Yep, that's the problem. I just tried a similar query myself. It asked me for the param value for "tot" until I removed it from the WHERE clause. Logically, I don't think it makes sense to use an aggregate in a WHERE clause. Just my opinion.

 

[boblarson]

For an aggregate you don't use a WHERE clause, you use a HAVING clause.