MS Access Parameter value error in simple query 1

[locloc]

in my query i have 2 calculated fields (SumVL and SumP4)i want to divide one by the other([SumVL]/[SumP4]). I get a message box "enter parameter value" for each itenm (SumVL and SumP4) . i don't enter any value, and then the query calculates correctly. how can i eliminate the parameter value message?

here is what it looks like in SQL:
SELECT Sum(QyP4vVLP4.VLTarget) AS SumVL, Sum(QyP4vVLP4.CalcP4) AS SumP4, QyP4vVLP4.Industry, [SumVL]/[SumP4] AS Ratio
FROM QyP4vVLP4
GROUP BY QyP4vVLP4.Industry, [SumVL]/[SumP4]
HAVING (((QyP4vVLP4.Industry)="ADVERT"));

 

[lameid]

I bet if you paste that SQL into a new query the problem goes away.

It sounds like you have a parameter defined in the QBE. Get the queries properties while in design view and see if there is a parameter property set to ask for SumVL and SumP4.

Although, in some aggregate queries I have seen access choke on aritmetic on aliases.

Ultimately, you might have to substitute each alias with its underlying expression but this one really feels like the parameter.

 

[locloc]

thanks - still not working - i copy/pasted the sql into a new query - same error.

i looked in the properties box and did not find anything like parameters? what is that called in the list of properties please?
thank you

 

[locloc]

Also, the underlying value (SumVL,SumP4) is a calculation of the sum of a number of records, made in another query - i can't think how to incorporate that into one sql statement.

 

[PHV]

What about this ?
SELECT Sum(VLTarget) AS SumVL, Sum(CalcP4) AS SumP4, Industry, Sum(VLTarget)/Sum(CalcP4) AS Ratio
FROM QyP4vVLP4
WHERE Industry='ADVERT'
GROUP BY Industry;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Woops, Parameter is off the Query menu... I misremembered it. However that is moot as pasting the SQL would have blanked it out.

PHV has the SQL statements substituted in the Select clause exactly as I was suggesting. That should definitely work.

 

[locloc]

Thank you - the new code works perfectly.