Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSQuery issue.

Posted on 2010-09-01
4
Medium Priority
?
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
J79123 earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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