Enter Parameter Value dialog

[billtubbs]

Hi

I am getting the common problem of the "Enter Parameter Value" dialog box, which pops up every time I execute a query. I built the query using the Expression Builder in Access. If any of my expressions reference another column in the same query, it causes the dialog box to appear for each parameter before displaying the query output, even though no parameter values are required.

For example, here are four expressions in my query:

Q_ELEC_1: [Ref case energy use by sector 2010]!E_ELEC_2010
Q_ELEC_2: [Adjusted price scenarios 2010]!E_ELEC_2010
deltaQELEC: [Q_ELEC_2]-[Q_ELEC_1]
ELEC_ELEC: IIf(([Q_ELEC_1]=0),Null,([deltaQELEC])/([Q_ELEC_1]*0.01))

These cause Enter Parameter Value dialogs for the three parameters Q_ELEC_1, Q_ELEC_2, and deltaQELEC.

Why doesn't the query recognise that these are defined elsewhere within the query and don't need parameter values?

[JKing]

Could you post the SQL for this query?

[billtubbs]

Could you post the SQL for this query?

Here is the SQL from the Access document report for the query (thanks).

Monday, July 23, 2007
Query: Elec Price Elasticities
 
SQL
 
SELECT [Adjusted price scenarios 2010].Sector, [Adjusted price scenarios 2010].SimName,
[Ref case energy use by sector 2010]![E_ELEC_2010] AS Q_ELEC_1, [Adjusted price
scenarios 2010]![E_ELEC_2010] AS Q_ELEC_2, [Q_ELEC_2]-[Q_ELEC_1] AS deltaQELEC,
IIf(([Q_ELEC_1]=0),Null,([deltaQELEC])/([Q_ELEC_1]*0.01)) AS ELEC_ELEC, [Ref case
energy use by sector 2010]![E_RPP_2010] AS Q_RPP_1, [Adjusted price scenarios
2010]![E_RPP_2010] AS Q_RPP_2, ([Q_RPP_2]-[Q_RPP_1]) AS deltaQRPP,
IIf([Q_RPP_1]=0,Null,([deltaQRPP])/([Q_RPP_1]*0.01)) AS RPP_ELEC, [Ref case energy use
by sector 2010]![E_COAL_2010] AS Q_COAL_1, [Adjusted price scenarios
2010]![E_COAL_2010] AS Q_COAL_2, ([Q_COAL_2]-[Q_COAL_1]) AS deltaQCOAL,
IIf([Q_COAL_1]=0,Null,([deltaQCOAL])/([Q_COAL_1]*0.01)) AS COAL_ELEC, [Ref case
energy use by sector 2010]![E_NG_2010] AS Q_NG_1, [Adjusted price scenarios
2010]![E_NG_2010] AS Q_NG_2, ([Q_NG_2]-[Q_NG_1]) AS deltaQNG,
IIf([Q_NG_1]=0,Null,([deltaQNG])/([Q_NG_1]*0.01)) AS NG_ELEC
FROM Cims_output_data, [Ref case energy use by sector 2010] INNER JOIN [Adjusted price
scenarios 2010] ON [Ref case energy use by sector 2010].Sector = [Adjusted price
scenarios 2010].Sector
GROUP BY [Adjusted price scenarios 2010].Sector, [Adjusted price scenarios
2010].SimName, [Ref case energy use by sector 2010]![E_ELEC_2010], [Adjusted price
scenarios 2010]![E_ELEC_2010], [Q_ELEC_2]-[Q_ELEC_1],
IIf(([Q_ELEC_1]=0),Null,([deltaQELEC])/([Q_ELEC_1]*0.01)), [Ref case energy use by sector
2010]![E_RPP_2010], [Adjusted price scenarios 2010]![E_RPP_2010], ([Q_RPP_2]-
[Q_RPP_1]), IIf([Q_RPP_1]=0,Null,([deltaQRPP])/([Q_RPP_1]*0.01)), [Ref case energy use by
sector 2010]![E_COAL_2010], [Adjusted price scenarios 2010]![E_COAL_2010],
([Q_COAL_2]-[Q_COAL_1]), IIf([Q_COAL_1]=0,Null,([deltaQCOAL])/([Q_COAL_1]*0.01)),
[Ref case energy use by sector 2010]![E_NG_2010], [Adjusted price scenarios