472,121 Members | 1,475 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,121 software developers and data experts.

Enter Parameter Value dialog


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?
Jul 23 '07 #1
2 3165
1,206 Expert 1GB
Could you post the SQL for this query?
Jul 23 '07 #2
Could you post the SQL for this query?
Here is the SQL from the Access document report for the query (thanks).

Expand|Select|Wrap|Line Numbers
  1. Monday, July 23, 2007
  2. Query: Elec Price Elasticities
  4. SQL
  6. SELECT [Adjusted price scenarios 2010].Sector, [Adjusted price scenarios 2010].SimName,
  7. [Ref case energy use by sector 2010]![E_ELEC_2010] AS Q_ELEC_1, [Adjusted price
  8. scenarios 2010]![E_ELEC_2010] AS Q_ELEC_2, [Q_ELEC_2]-[Q_ELEC_1] AS deltaQELEC,
  9. IIf(([Q_ELEC_1]=0),Null,([deltaQELEC])/([Q_ELEC_1]*0.01)) AS ELEC_ELEC, [Ref case
  10. energy use by sector 2010]![E_RPP_2010] AS Q_RPP_1, [Adjusted price scenarios
  11. 2010]![E_RPP_2010] AS Q_RPP_2, ([Q_RPP_2]-[Q_RPP_1]) AS deltaQRPP,
  12. IIf([Q_RPP_1]=0,Null,([deltaQRPP])/([Q_RPP_1]*0.01)) AS RPP_ELEC, [Ref case energy use
  13. by sector 2010]![E_COAL_2010] AS Q_COAL_1, [Adjusted price scenarios
  14. 2010]![E_COAL_2010] AS Q_COAL_2, ([Q_COAL_2]-[Q_COAL_1]) AS deltaQCOAL,
  15. IIf([Q_COAL_1]=0,Null,([deltaQCOAL])/([Q_COAL_1]*0.01)) AS COAL_ELEC, [Ref case
  16. energy use by sector 2010]![E_NG_2010] AS Q_NG_1, [Adjusted price scenarios
  17. 2010]![E_NG_2010] AS Q_NG_2, ([Q_NG_2]-[Q_NG_1]) AS deltaQNG,
  18. IIf([Q_NG_1]=0,Null,([deltaQNG])/([Q_NG_1]*0.01)) AS NG_ELEC
  19. FROM Cims_output_data, [Ref case energy use by sector 2010] INNER JOIN [Adjusted price
  20. scenarios 2010] ON [Ref case energy use by sector 2010].Sector = [Adjusted price
  21. scenarios 2010].Sector
  22. GROUP BY [Adjusted price scenarios 2010].Sector, [Adjusted price scenarios
  23. 2010].SimName, [Ref case energy use by sector 2010]![E_ELEC_2010], [Adjusted price
  24. scenarios 2010]![E_ELEC_2010], [Q_ELEC_2]-[Q_ELEC_1],
  25. IIf(([Q_ELEC_1]=0),Null,([deltaQELEC])/([Q_ELEC_1]*0.01)), [Ref case energy use by sector
  26. 2010]![E_RPP_2010], [Adjusted price scenarios 2010]![E_RPP_2010], ([Q_RPP_2]-
  27. [Q_RPP_1]), IIf([Q_RPP_1]=0,Null,([deltaQRPP])/([Q_RPP_1]*0.01)), [Ref case energy use by
  28. sector 2010]![E_COAL_2010], [Adjusted price scenarios 2010]![E_COAL_2010],
  29. ([Q_COAL_2]-[Q_COAL_1]), IIf([Q_COAL_1]=0,Null,([deltaQCOAL])/([Q_COAL_1]*0.01)),
  30. [Ref case energy use by sector 2010]![E_NG_2010], [Adjusted price scenarios
Jul 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.