By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Enter Parameter Value dialog

P: 2
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?
Jul 23 '07 #1
Share this Question
Share on Google+
2 Replies


JKing
Expert 100+
P: 1,206
Could you post the SQL for this query?
Jul 23 '07 #2

P: 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
  3.  
  4. SQL
  5.  
  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
  31.  
Jul 23 '07 #3

Post your reply

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