back_in_the_1980s
New Member
- Joined
- Jul 28, 2016
- Messages
- 5
Hello:
I have a SQL query that I wrote that begins with the following declaration:
DECLARE @DATE DATETIME.
I do not have this code saved as a view or a stored procedure nor do I want to do so.
I simply want to use Excel 2010's Microsoft Query function to pull data from SQL through my view and allow users to enter a date in a spreadsheet and have that date passed to the @DATE variable in my code.
@DATE is used throughout my query (code), as a variable for "date" and again this date is to be entered by the end user and compared with other date fields in the query.
After getting my data into Excel, through Microsoft query and by replacing @DATE with a hard-coded date value, I click the "Data" menu selection and go to "Connections".
At the bottom, in the "Definition" section, I replace the query in that section with my query containing again the declaration of @DATE that I mentioned earlier. And, I replace the hard-coded dates with the @DATE SQL variable.
I'd like to, at that point, have the @DATE variable from my code "equal" a date that I place in a cell in my spreadsheet.
But, no matter how many times and ways I try this, I cannot get Excel 2010's Microsoft Query to communicate with this SQL @DATE variable.
What am I doing wrong?
Again, I don't want to use a stored procedure or VBA. I'm trying to keep this simple.
Can Microsoft Query simply not communicate with a SQL @ variable?
It almost sounds like the best that I can do is to keep my query the way it is in Microsoft Query (using the @DATE variable) and type the following SQL statement after the SQL declaration statement in the beginning:
SET @DATE = '2016-09-30 00:00:00.000'
I suppose, then, I can have the end users go into the Connection Properties section and simply change the date in that "SET" statement to whatever date they would like.
I would love to be able to tell my users that they can simply enter a date into a cell and click "Refresh All". But, again, my guess is that Excel's Microsoft Query cannot communicate with SQL @ variables.
Does anyone have any ideas or thoughts on this?
Thanks! Much appreciated!
John
I have a SQL query that I wrote that begins with the following declaration:
DECLARE @DATE DATETIME.
I do not have this code saved as a view or a stored procedure nor do I want to do so.
I simply want to use Excel 2010's Microsoft Query function to pull data from SQL through my view and allow users to enter a date in a spreadsheet and have that date passed to the @DATE variable in my code.
@DATE is used throughout my query (code), as a variable for "date" and again this date is to be entered by the end user and compared with other date fields in the query.
After getting my data into Excel, through Microsoft query and by replacing @DATE with a hard-coded date value, I click the "Data" menu selection and go to "Connections".
At the bottom, in the "Definition" section, I replace the query in that section with my query containing again the declaration of @DATE that I mentioned earlier. And, I replace the hard-coded dates with the @DATE SQL variable.
I'd like to, at that point, have the @DATE variable from my code "equal" a date that I place in a cell in my spreadsheet.
But, no matter how many times and ways I try this, I cannot get Excel 2010's Microsoft Query to communicate with this SQL @DATE variable.
What am I doing wrong?
Again, I don't want to use a stored procedure or VBA. I'm trying to keep this simple.
Can Microsoft Query simply not communicate with a SQL @ variable?
It almost sounds like the best that I can do is to keep my query the way it is in Microsoft Query (using the @DATE variable) and type the following SQL statement after the SQL declaration statement in the beginning:
SET @DATE = '2016-09-30 00:00:00.000'
I suppose, then, I can have the end users go into the Connection Properties section and simply change the date in that "SET" statement to whatever date they would like.
I would love to be able to tell my users that they can simply enter a date into a cell and click "Refresh All". But, again, my guess is that Excel's Microsoft Query cannot communicate with SQL @ variables.
Does anyone have any ideas or thoughts on this?
Thanks! Much appreciated!
John
