Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2348
  • Last Modified:

Excel SQL Query of an External SQL table with parameters

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
GPSPOW
Asked:
GPSPOW
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Robberbaron (robr)Commented:
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
 
Eugene ZCommented:
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
 
GPSPOWAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Eugene ZCommented:
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
 
Vadim RappCommented:
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
 
GPSPOWAuthor Commented:
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
 
Vadim RappCommented:
> 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
 
Eugene ZCommented:
<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
 
GPSPOWAuthor Commented:
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
 
Robberbaron (robr)Commented:
please close this question.
0
 
Vadim RappCommented:
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
 
GPSPOWAuthor Commented:
I used a variation of this solution.  I had to change the format number but I got it to work.

Glen
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now