Excel SQL Query of an External SQL table with parameters

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

Select allOpen in new window

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

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"')

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.

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

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

<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

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

please close this question.

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.

I used a variation of this solution.  I had to change the format number but I got it to work.

Glen