How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

(1) Share

Report

Posted on by Community Member Microsoft Employee

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/stored procedure nor do I want to do so.

I simply want to use Excel 2010 Microsoft Query 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 in Microsoft Query.

@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.

When I walk through the Microsoft Query wizard, I choose some table that contains a date field. Then, instead of returning data to Excel right away, I "put myself" in that window where you can add criteria. (I forget the window's name.)

So, I use that window to create a parameter "placeholder" called [SumDate]. Then, I return the data to Excel.

Afterward, I place a date in a cell in the spreadsheet. Then, 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 code (query).

Then I click the "Parameters" button to tell Excel to pull the date from that spreadsheet cell that I mentioned.

So, if you think about it, @DATE from my code is to equal the parameter that again is to represent a date that I place in a cell in my spreadsheet.

But, Excel is not paying any attention to this parameter.

What am I doing wrong? Why can I not get this parameter to pull from the spreadsheet to "populate" my code's @DATE variable?

Also, I don't want to use a stored procedure or VBA. I'm trying to keep this simple.

I just need to know the basics of doing so, after I paste my query into the Data...Connections...Properties...Command section.

That's really what I need.

Thanks! Much appreciated!

John

*This post is locked for comments

All responses (4)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Thanks, LiSpeedyG! The following is my code:

DECLARE @DATE DATETIME

SET @DATE = '2016-09-30 00:00:00.000'

select RM20101.CUSTNMBR as [CUSTOMER ID], RM00101.CUSTNAME AS [NAME], RM00101.PYMTRMID AS [PAYMENT TERMS],

CASE RM20101.RMDTYPAL

WHEN 1 then 'Invoice'

WHEN 3 then 'Debit Memo'

WHEN 4 then 'Finance Charge'

WHEN 5 then 'Service Repair'

WHEN 6 then 'Warranty'

WHEN 7 then 'Credit Memo'

WHEN 8 then 'Return'

WHEN 9 then 'Payment'

ELSE 'Other'

END as [DOC TYPE],

RM20101.DOCNUMBR as [DOC NUMBER],

CONVERT(VARCHAR(10),RM20101.DOCDATE,101) as [DOC DATE],

CASE CAST(RM20101.DUEDATE as DATE) WHEN '01/01/1900' then NULL else convert(varchar(10), RM20101.DUEDATE, 101)

END as [DUE DATE],

CASE WHEN RM20101.RMDTYPAL < 7 THEN

RM20101.CURTRXAM ELSE RM20101.CURTRXAM * -1

END

as [CURTRXAM],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 0

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 0

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [CURRENT],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 1

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 30

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) = 1

AND DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 30

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [1-30 DAYS],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 31

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 60

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) = 31

AND DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 60

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [31-60 DAYS],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 61

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 90

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) = 61

AND DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 90

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [61-90 DAYS],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 91

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 180

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) = 91

AND DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 180

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [91 - 180 DAYS],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 181

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 364

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) = 181

AND DATEDIFF(dd, RM20101.DUEDATE, @DATE) <= 364

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [181-364 DAYS],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) >= 365

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) >= 365

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [365 AND OVER],

CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END AS [>180],

CASE WHEN (CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END) > 0 THEN

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180

and RM20101.RMDTYPAL < 7

THEN RM20101.CURTRXAM ELSE 0

END) ELSE 0 END AS [BAD DEBT],

SUM((CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 0

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 1

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 30

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 31

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 60

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 61

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 90

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 91

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 180

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 181

AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 364

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END) +

(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) >= 365

and RM20101.RMDTYPAL > 6

THEN RM20101.CURTRXAM * - 1

ELSE 0

END)) as [BACK OUT CREDITS],

CN00500.CRDTMGR as [CRDTMGR],

RM00101.COMMENT1 AS [COMMENT 1], RM00101.COMMENT2 AS [COMMENT 2]

from RM20101

INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR

LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR

WHERE (RM20101.VOIDSTTS = 0) AND (RM20101.CURTRXAM <> 0)

GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, RM20101.RMDTYPAL, RM20101.DOCNUMBR, RM20101.DOCDATE, RM20101.DUEDATE, RM20101.ORTRXAMT,

RM20101.CURTRXAM, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2

ORDER BY RM20101.CUSTNMBR
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Perhaps if you can post your query we can have a look. The above Process does work.
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Hi LISpeedyG:

Yes, I have tried all of this, over the last few days from articles that I read online. I tried again and, as usual, I get syntax errors such as "Invalid parameter number" and "Invalid Indexes".

It is really weird. All of the instructions that I read make it look so easy, yet, I can't get this to work.

I just think that Microsoft Query simply cannot 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.

It may also have to do with the fact that my @DATE variable is not in the where clause but resides within SQL If, Then (SELECT CASE) statements comparing itself to fields like RM20101.DUEDATE and RM20101.DOCDATE.

If you have any further thoughts or suggestions, I'd appreciate it! Thanks, again! :)

John
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Hi John,

If I understand you correctly, you want to use a data placed on a spreadsheet in a SQL query that returns GP data to a spreadsheet. If this is your goal the process is fairly straightforward:

1. Using Excel Microsoft Query you can define a filter parameter for the date you want to parameterize, i.e. your @date

2. In the Filter field type [] (right and left square brackets)

3. you will receive an input box. Just enter a representative date and <enter>

4. Exit your Query Editor

5. Enter the "Connection Properties" dialogue box. Here you will notice your query has been entered under the "Command Text" box.

6. You will notice a "?" entered in the query that represents your parameterized input for the date. It will look like something like the following example SELECT * FROM svc05000 WHERE (svc05000.ENTDTE=?)

7. You will now notice that a "parameters" button is enables at the bottom of the "Connection Properties" dialogue box. Press it.

8. In this dialog you will be asked where the data can be found for the parameter. Here you can choose the cell you will place the date.

9. Once you point to the correct cell (be sure it contains data prior to completing the process). you can then point to where you want the returned SQL data table.

10. If you enabled the Refresh data on change you should then receive your new data on date change.

11. I've included some screenshots below. And, let me know if this resolves your problems.

Gus

How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

How to Have Microsoft Query Pull a Variable from a SQL Query and Populate that Variable from Excel

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates