Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel SQL Query of an External SQL table with parameters

Posted on 2014-01-04
12
Medium Priority
?
2,327 Views
Last Modified: 2014-02-11
Select [Attending Physician],[AdmitDateTime],[Patient Name],[Account Number]
From

"livedb"."PATIENTSMC\gpowers"."tbl_Midnight_Stay_Admissions"

Above is the SQL statement to retrieve data from a SQL table into a workbook tab.  I want the user to input a beginning and ending date into two workbook cells, let's say Sheet2.B2 and Sheet2.B3.

What is the syntax to modify the statement to use the values in these two cells to create the WHERRE clause for the SQL statement above?

Thanks

glen
0
Comment
Question by:GPSPOW
[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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39757087
1. how do you process the query now ?  Query object or pure VBA connection to the database?

2. the proper way is to use Parameters within your SQL syntax but the quick and very dirty (google SQL Injection) is to use the dates with the sql.

some examples. plenty online as well.

Sub GetData()

    Dim sqlQ As String
    Dim dateBegin As String, dateEnd As String
    dateBegin = Format(Range("Sheet2!B2").Value, "dd/mmm/yyyy")
    dateEnd = Format(Range("Sheet2!B3").Value, "dd/mmm/yyyy")
    
    sqlQ = "SELECT [Attending Physician],[AdmitDateTime],[Patient Name],[Account Number]" & _
           " FROM livedb.PATIENTSMC\gpowers.tbl_Midnight_Stay_Admissions" & _
           " WHERE [AdmitDateTime] => " & dateBegin & " AND [AdmitDateTime] <= " & dateEnd & _
           ";"
    
    Dim qq As QueryTable
    qq.CommandType = xlCmdSql
    qq.CommandText = sqlQ
    qq.Connection = "your DB connection string"
    qq.Refresh
    
    'or using the methods from Excel help
    sqlQ = "SELECT [Attending Physician],[AdmitDateTime],[Patient Name],[Account Number]" & _
           " FROM livedb.PATIENTSMC\gpowers.tbl_Midnight_Stay_Admissions" & _
           " WHERE ([AdmitDateTime] => ? ) AND ([AdmitDateTime] <= ?);"
    
    Dim qt As Variant
    Set qt = Sheets("sheet1").QueryTables(1)
    qt.Sql = sqlQ
    Set param1 = qt.Parameters.Add("Start Date", _
        xlParamTypeDate)
    param1.SetParam xlConstant, dateBegin
    Set param2 = qt.Parameters.Add("End Date", _
        xlParamTypeDate)
    param2.SetParam xlConstant, dateEnd
    qt.Refresh
    
    
End Sub

Open in new window

0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 39757467
it is not clear what are in your case begging and end dates
but as idea: check if this "

Select [Attending Physician],[AdmitDateTime],[Patient Name],[Account Number]
From "livedb"."PATIENTSMC\gpowers"."tbl_Midnight_Stay_Admissions

Union ALL

select NULL, CONVERT (Varchar(10),Getdate() ,112)  "Beginning Date" ,  NULL, NULL

Union ALL

select NULL, CONVERT (Varchar(10),Getdate() ,112)  "Ending Date" , NULL, NULL
0
 

Author Comment

by:GPSPOW
ID: 39757472
Can you tell me what is wrong with this SQL statement:

SELECT BarVisits.AccountNumber, BarVisits.Name,  convert(datetime,BarVisits.AdmitDateTime,101) AS 'AdmDt',BarVisitFinancialData2.AttendProviderName
FROM livedb.dbo.BarVisitFinancialData2 BarVisitFinancialData2, livedb.dbo.BarVisits BarVisits, livedb.dbo.DMisInsurance DMisInsurance
WHERE BarVisits.VisitID = BarVisitFinancialData2.VisitID AND BarVisits.PrimaryInsuranceID = DMisInsurance.InsuranceID AND ((DMisInsurance.DefaultFinancialClassID In ('MCR','MCR HMO')) AND (BarVisits.InpatientOrOutpatient='I') AND (BarVisits.AdmitDateTime>{ts '2013-12-31 00:00:00'})) AND convert(datetime,BarVisits.AdmitDateTime,101)  > range("Sheet2!b3").value


The last where statement is not valid.  The value in Sheet2!B3 is a date 1/2/2014.

thanks

Glen
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 39757494
what is the error? are you running from Excel?

range("Sheet2!b3").value is Excel
sql server does not know what it is
you may need to use Excel Macros
\VBA

check
Use Microsoft Query to retrieve external data
http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx


you can try

convert(datetime,BarVisits.AdmitDateTime,101)  >  (
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\yourexcel.xls',
                'select * from "Sheet2!b3"')
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39757572
1. In Excel, open Query (menu Data -> Extermnal Data -> New Database Query) and create your sql statement; specify the criteria you want to be parameterized as [parm] in the designer. You can look up Help in Query by pressing F1, and read how to create query with parameter. Once the query is correct, the menu item "Parameters" in Query will be enabled, and you will be able to run it there and see the prompt for parameter value.
2. return query results to Excel.
3. in Excel, select the data range, right-click, and select item "Parameters", which should be now enabled.
4. select "get the value from the following cell" and specify the cell to take the parameter from.
0
 

Author Comment

by:GPSPOW
ID: 39757677
AdmitDateTime                        AdmDt
2014-01-01 04:14:00.000      2014-01-01      I      MCR HMO
2014-01-01 13:30:00.000      2014-01-01      I      MCR
2014-01-01 12:03:00.000      2014-01-01      I      MCR
2014-01-01 15:39:00.000      2014-01-01      I      MCR HMO
2014-01-01 18:24:00.000      2014-01-01      I      MCR HMO
2014-01-01 16:08:00.000      2014-01-01      I      MCR HMO
2014-01-01 22:45:00.000      2014-01-01      I      MCR HMO
2014-01-01 14:33:00.000      2014-01-01      I      MCR
2014-01-02 20:38:00.000      2014-01-02      I      MCR HMO
2014-01-01 19:10:00.000      2014-01-01      I      MCR

MS-Query SQL:

SELECT BarVisits.AccountNumber, BarVisits.Name, BarVisits.AdmitDateTime, convert(date,BarVisits.AdmitDateTime,101) AS 'AdmDt', BarVisits.InpatientOrOutpatient, DMisInsurance.DefaultFinancialClassID, BarVisitFinancialData2.AttendProviderName
FROM livedb.dbo.BarVisitFinancialData2 BarVisitFinancialData2, livedb.dbo.BarVisits BarVisits, livedb.dbo.DMisInsurance DMisInsurance
WHERE BarVisits.VisitID = BarVisitFinancialData2.VisitID AND BarVisits.PrimaryInsuranceID = DMisInsurance.InsuranceID AND ((DMisInsurance.DefaultFinancialClassID In ('MCR','MCR HMO')) AND (BarVisits.InpatientOrOutpatient='I') AND (BarVisits.AdmitDateTime>{ts '2013-12-31 00:00:00'} And BarVisits.AdmitDateTime>=? And BarVisits.AdmitDateTime<=?))



I am getting closer to a resolution on this.

When I ask for Date From = 2014-01-02 and Date Through 2014-01-02 I do not get any data.  As yoou can see the second to last record satifies this parameter.  I know it has something to do the the fact the AdmitDateTime is a DateTime format.

What is parameter I need for the AdmitDateTime to prompt the user to enter a date in the format "MM/DD/YYYY" and use the date portion of the field?

Thanks

glen
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39757806
> When I ask for Date From = 2014-01-02 and Date Through 2014-01-02 I do not get any data.  As yoou can see the second to last record satifies this parameter.

No, it does not. Without time part, your criteria is "from 2014-01-02 00:00:00 to 2014-01-02 00:00:00".
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39758023
<What is parameter I need for the AdmitDateTime to prompt the user to enter a date in the format "MM/DD/YYYY" and use the date portion of the field?>
yes
0
 

Author Comment

by:GPSPOW
ID: 39758131
Thanks to all who answered.

I finally found my own solution by creating a Ms-Query with a parameter referencing a formatted cell in the workbook.

The cell's format matches the format of the field.  

Again thanks
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39838590
please close this question.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39838630
To close the question, please check Help and search for "close question", you will see how to accept your own answer and how to accept a comment or several experts' comments.

If I understand correctly, you resolved the problem by creating query with parameters, which is what was suggested in comments ID: 39757494 and ID: 39757572 above.
0
 

Author Closing Comment

by:GPSPOW
ID: 39851915
I used a variation of this solution.  I had to change the format number but I got it to work.

Glen
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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