Solved

Excel SQL Query of an External SQL table with parameters

Posted on 2014-01-04
12
2,174 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
  • 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 42

Accepted Solution

by:
EugeneZ earned 500 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
 
LVL 42

Expert Comment

by:EugeneZ
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 42

Expert Comment

by:EugeneZ
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now