Solved

MSQuery issue.

Posted on 2010-09-01
4
410 Views
Last Modified: 2012-05-10
I have an excel spreadsheet with data that was inserted into a table from an ODBC source. Everything works fine until I use a criteria to filter the data. I am able to return the results the first time with the criteria filter, however when I then attempt to change criteria value or field I have issues.
I right click on the table in excel and select table, then edit query, and I am given a message from Microsoft Query stating "This query cannot be edited by the Query Wizard". If I press OK the Microsoft Query screen opens without the tables section on the top of the view, and all of the options to add a table or criteria from the drop down menus are greyed ou, and the toolbar buttons are unresponsivet. I am unable to edit the query.

Does anyone know of a solution to this issue? At the moment each query has to be recreated each time I need to change the criteria.
0
Comment
Question by:adowding
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
J79123 earned 500 total points
ID: 33583905
This happens when the query is too complex to use the visual editor. It's not lost, it just seems like it.  The SQL statement still exists. you can view the SQL query that is pulling the data. I believe the option to edit the sql query is under the file or edit menu.
If it is just the criteria that is changing, and it is something you plan on changing frequently,  you can change the sql statement to use parameters (cell values) so that you can change the parameters in excel without opening up msquery and editing the SQL statement each time.
To do this, everywhere in the SQL statement that references a value, say
Select from xxx where yyy=#
change the value of the number to a ? And save it or hit ok.

basically, the ? In a SQL statement will tell excel that those are variables/parameters.

When you do this, the query data will go blank because the sql doesnt know quite what to pull yet, then you have to exit back to excel. Then in the properties of the data link, you will have the option to edit the parameter you set (the ? Value) and assign it to a cell.

It's sort've difficult for me to paint this how-to picture without an example. If you can give an example of the query and you need to be able change or update, I'm sure myself or someone else maybe able to give you more precise information.

But the short answer to your question is that, the data pull you are using is too complex to use just the visual editor (the tables etc.) and you need to edit the SQL statement.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33585928
J79123 is correct that the query is not lost when the SQL statement is too complex for the user interface to display.  Something like an INNER JOIN will usually cause it.  The SQL button will be functional and from the menus it is under VIEW / SQL... From there you can change the SQL statement but the SQL verbs are limited.

However - if the SQL statement is too complex to display in the User Interface the Parameters option will not function.  I do not believe you will have the option to use the "?" in the SQL statement.

If you are pulling your data from an ODBC source such as SQL Server or Access then it may be easier to create a VIEW on the main source that handles the complex SQL and then call that VIEW from MS-Query.  Then you can use the built in Parameters that J79123  described above.
0
 
LVL 6

Expert Comment

by:J79123
ID: 33587414
Actually you do get some sort of message about parameters, But it's misleading, because you can use them.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33587989
J79123 - You are correct...   I found a way to force the parameters to work with a query that would not display in the user interface.  Thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remove dups 10 37
Excel Conditional Statements 11 40
excel formula to sum column 13 15
Need excel formula correction. 5 14
Outlook Free & Paid Tools
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question