• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

?? query with a Declaired Variable shows data in the MS Query window but return no lines Excel?

I have a query that runs super fast in SQL Studio.  I have copied this into MS query and it shows the results in teh MS query window, but when I click return data nothing is returned to Excel.  If I comment out my variable statement it works just fine...
My statement is in the first 2 lines and is"
Declare @EmployerCode varchar(10)
Set @EmployerCode = 'FCH'

0
kgittinger
Asked:
kgittinger
  • 5
  • 5
1 Solution
 
Rory ArchibaldCommented:
I don't believe that querytables support that sort of declaration. Can you not create an SP (or view) in the SQL database and simply execute that?
0
 
kgittingerAuthor Commented:
I cannot create a view since the database is at a third party site...

0
 
Rory ArchibaldCommented:
Ah. I'd probably use code to change the commandtext of the querytable then. That way you can effectively hardcode the variables into the SQL string each time.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
kgittingerAuthor Commented:
That is where I started...I am trying to get to the point where I can have a parameter field in a cell in excel for the user to change. Since my query is a union all it is giving me trouble in the union select
0
 
Rory ArchibaldCommented:
It shouldn't be a problem in code because you aren't really using parameters as such - the whole SQL string gets rebuilt including the range value as a literal value within the string. So along the lines of:
Activesheet.querytables(1).commandtext = "SELECT blah from yadda where foo = '" & range("A1").value & " UNION ALL Select summat from elsewhere where foo2 = '" & range("A1").value & "'"
0
 
kgittingerAuthor Commented:
I will give it a whirl. Thanks
0
 
Rory ArchibaldCommented:
If you add the code into a worksheet_change event then you can have the table updated automatically when a different value is typed into your criteria cell.
0
 
kgittingerAuthor Commented:
Thanks.  If I need more help I will ask a related question  I may need help tweeking my SQL  I am a beginner
0
 
kgittingerAuthor Commented:
I do need help in how to have my SQL refernce information from a page in a workbook.  Please let me know when you are availble so I can post my SQL for assistance.

0
 
Rory ArchibaldCommented:
I suggest you post it as a related question, then everyone can chime in as needed. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now