LinkBack URL
About LinkBacks
Dear all
is there a way if we can insert a formula into SQL Command text? For example how can we insert this formula =Sum((A1:A25) + (B1:B25)) into excel query SQL Command text?
kind reagsrd
Dear all
is there a way if we can insert a formula into SQL Command text? For example how can we insert this formula =Sum((A1:A25) + (B1:B25)) into excel query SQL Command text?
kind reagsrd
This link: http://www.utteraccess.com/wiki/index.php/MS_Query has a discussion on how to use an excel table, the SUBSTITUTE command and some very short VB Code to rewrite the command string for a query.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
thanks for your reply , I couldn't work this out
No, there isn't. SQL and Excel work in completely different ways, you'd need to learn how to write the SQL for whatever it is you're trying to do
Maybe an example will help. Attached is a file. Pretend that you had created a very simple query with a connection name "MyConnectionName" that returns data to the Data sheet.
Now you want to create a dynamic query to return data to that same sheet.
On the control column sheet, I have data in columns E and F that need to be summed and used as a parameter in the query. For extra measure, I included a data that might also be part of the query.
The sum of columns E and F are in Cell B1 on the Control Panel page.
The date itself =TODAY()-1 (yesterday's date) is in Cell B2. However, this date is not in the format needed for a query. In fact it will show up as a date serial number such as 42422.
So in Cell B3, I format the date into a format expected by the query.
On the sheet SQL I have pseudo code in Column A. You will note that I have two "tokens" called &MyVariable and &Date (note that date is in single quotes - these are usually required by the SQL).
All this information is in an Excel table called Table_SQL.
In Column B I have the formula: =SUBSTITUTE(SUBSTITUTE([@[Raw SQL]],"&MyVariable",'Control Panel'!$B$1),"&Date",'Control Panel'!$B$3)
What this does is takes the String &MyVariable and sets it to the value in Cell B1 of the Control Panel. It also converts &Date into the value contained in cell B3 of the Control Panel. Please note SUBSTITUTE is case sensitive.
Cell B1 on the SQL page has the name SQLString. The formula is =SuperCat(Table_SQL[Translated SQL]). This is the new command string you want to use for the query.
Then we have the following code in module SubSQL. All you have to do is run the macro Refresh Data, and it will substitute the new SQL for the old SQL and refresh the query.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks