Why is my query asking for a paramater?

[Leslie Isaacs]

Hello All

I have the following query, which works OK in that it does give the correct
output, BUT when I initially run it I am asked for the parameter [Annual WTE
salary]. But this value is calculated, correctly, as another field in the
query. If I just hit the enter key the query runs, and the correct output is
generated: but why is it asking me to enter a value for [Annual WTE salary]?

Hope someone can help

Many thanks
Leslie Isaacs

The query:

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12) AS [Annual WTE
salary], [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed] ON staffs.name = [x
confirmed].name) ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

 

[J_Goddard via AccessMonster.com]

Hi -

It's probably because you have put square brackets around [Annual WTE salary].
Access cannot find this as a table field, so it thinks this is a parameter,
and asks for a value. Try using Annual_WTE_salary, without the brackets.

John

Hello All

I have the following query, which works OK in that it does give the correct
output, BUT when I initially run it I am asked for the parameter [Annual WTE
salary]. But this value is calculated, correctly, as another field in the
query. If I just hit the enter key the query runs, and the correct output is
generated: but why is it asking me to enter a value for [Annual WTE salary]?

Hope someone can help

Many thanks
Leslie Isaacs

The query:

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12) AS [Annual WTE
salary], [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed] ON staffs.name = [x
confirmed].name) ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

 

[John W. Vinson]

I have the following query, which works OK in that it does give the correct
output, BUT when I initially run it I am asked for the parameter [Annual WTE
salary]. But this value is calculated, correctly, as another field in the
query.

Usually (not always, it works often enough to mislead you!) you cannot use a
calculated field in a further calculation; you will need to recapitulate the
expression used for [Annual WTE Salary] in the expression for the second
field, rather than referencing the calculated fieldname.

 

[John Spencer]

The problem is caused by you calculating Annual WTE Salary. The expression
is not known for later calculations. In some versions of Access you would
have to repeat the calculation every time it was used. So your IIF
statements would be have to include the SUM calculation.

You might also check if you have applied and order by or filter criteria to
the query. I might Copy the SQL into a new query and see if that got rid of
the request.

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12)
AS [Annual WTE salary]
, [x confirmed].name, staffs.practice, months.year, months.[ha year]
, staffs.[nhsp ees rate]
, IIf([nhsp ees rate]=0.05,0.05
,IIf([Annual WTE salary]>100000,0.085
,IIf([Annual WTE salary]>63417,0.075
,IIf([Annual WTE salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed]
ON staffs.name = [x confirmed].name)
ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello All

I have the following query, which works OK in that it does give the
correct
output, BUT when I initially run it I am asked for the parameter [Annual
WTE
salary]. But this value is calculated, correctly, as another field in the
query. If I just hit the enter key the query runs, and the correct output
is
generated: but why is it asking me to enter a value for [Annual WTE
salary]?

Hope someone can help

Many thanks
Leslie Isaacs

The query:

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12) AS [Annual WTE
salary], [x confirmed].name, staffs.practice, months.year, months.[ha
year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed] ON staffs.name =
[x
confirmed].name) ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha
year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

 

[Ruth Isaacs]

Hello John

Thanks for your reply.
Getting rid of the square brackets and using Annual_WTE_salary worked
great!!
Thanks again.

Les

Hi -

It's probably because you have put square brackets around [Annual WTE salary].
Access cannot find this as a table field, so it thinks this is a parameter,
and asks for a value. Try using Annual_WTE_salary, without the brackets.

John

Hello All

I have the following query, which works OK in that it does give the correct
output, BUT when I initially run it I am asked for the parameter [Annual WTE
salary]. But this value is calculated, correctly, as another field in the
query. If I just hit the enter key the query runs, and the correct output is
generated: but why is it asking me to enter a value for [Annual WTE salary]?

Hope someone can help

Many thanks
Leslie Isaacs

The query:

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12) AS [Annual WTE
salary], [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed] ON staffs.name = [x
confirmed].name) ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

 

[Ruth Isaacs]

Hello John

Thanks for your reply.

In fact I had tried substituting the calculated expression for the field
name, but then I got a "query too complex" error!
I have managed to get the query to run without asking for the paramater by
getting rid of the square brackets and altering the name to get rid of the
spaces - as per John Goddard's suggestion. Isn't access fussy!

Thanks again
Les

I have the following query, which works OK in that it does give the correct
output, BUT when I initially run it I am asked for the parameter [Annual WTE
salary]. But this value is calculated, correctly, as another field in the
query.

Usually (not always, it works often enough to mislead you!) you cannot use a
calculated field in a further calculation; you will need to recapitulate the
expression used for [Annual WTE Salary] in the expression for the second
field, rather than referencing the calculated fieldname.

 

[Ruth Isaacs]

Hello John

Thanks for your reply.

In fact I had tried substituting the calculated expression for the field
name, but then I got a "query too complex" error!
I have managed to get the query to run without asking for the paramater by
getting rid of the square brackets and altering the name to get rid of the
spaces - as per John Goddard's suggestion. Isn't access fussy!

Thanks again
Les

The problem is caused by you calculating Annual WTE Salary. The expression
is not known for later calculations. In some versions of Access you would
have to repeat the calculation every time it was used. So your IIF
statements would be have to include the SUM calculation.

You might also check if you have applied and order by or filter criteria to
the query. I might Copy the SQL into a new query and see if that got rid of
the request.

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12)
AS [Annual WTE salary]
, [x confirmed].name, staffs.practice, months.year, months.[ha year]
, staffs.[nhsp ees rate]
, IIf([nhsp ees rate]=0.05,0.05
,IIf([Annual WTE salary]>100000,0.085
,IIf([Annual WTE salary]>63417,0.075
,IIf([Annual WTE salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed]
ON staffs.name = [x confirmed].name)
ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello All

I have the following query, which works OK in that it does give the
correct
output, BUT when I initially run it I am asked for the parameter [Annual
WTE
salary]. But this value is calculated, correctly, as another field in the
query. If I just hit the enter key the query runs, and the correct output
is
generated: but why is it asking me to enter a value for [Annual WTE
salary]?

Hope someone can help

Many thanks
Leslie Isaacs

The query:

SELECT Sum([x confirmed].[hourly rate]*[wte]/7*365/12) AS [Annual WTE
salary], [x confirmed].name, staffs.practice, months.year, months.[ha
year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05)))) AS Expr1
FROM months INNER JOIN (staffs INNER JOIN [x confirmed] ON staffs.name =
[x
confirmed].name) ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]))
GROUP BY [x confirmed].name, staffs.practice, months.year, months.[ha
year],
staffs.[nhsp ees rate], IIf([nhsp ees rate]=0.05,0.05,IIf([Annual WTE
salary]>100000,0.085,IIf([Annual WTE salary]>63417,0.075,IIf([Annual WTE
salary]>19165,0.065,0.05))))
HAVING (((months.year)=[Forms]![frm x main]![year]));