How to substitute dates using cell reference in SQL query using VBA

Hi,


I am trying to use VBA to perform a query on our SQL2005 server but I would like to use dates entered in cells B1 and B2 to replace the START_DATE in the query instead of hard coding the dates. The START_DATE field is formatted as datetime and our system uses the dd/mm/yyyy hh:mm:ss format. When I run the code below, it is working fine but I am not sure how I can replace the START_DATE (e.g. entered as 01/01/2012 dd/mm/yyyy) with cell references. Any help would be much appreciated. Thanks.

Sub Data()

Application.ScreenUpdating = False
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=TESTDB;Description=TESTDB;UID=test;PWD=test;APP=Microsoft Office XP;WSID=NMLWS200611738;DATABASE=TESTDB;A" _
        ), Array("nsiNPW=No;")), Destination:=Range("A8"))
        .CommandText = Array( _
        "SELECT s.TEXT_ID AS SAMPLE_ID, s.SAMPLE_NAME AS REFERENCE, s.DESCRIPTION , CAST(r.FORMATTED_ENTRY AS FLOAT) AS RESULT, u.DISPLAY_STRING AS UNITS" & Chr(13) & "" & Chr(10) & "FROM TESTDB.dbo.result r, TESTDB.dbo.sample s, TESTDB.dbo.test t, TESTDB.dbo.units u" & Chr(13) & "" & Chr(10) & "W" _
        , _
        "HERE s.SAMPLE_NUMBER = t.SAMPLE_NUMBER " & Chr(13) & "" & Chr(10) & "AND t.TEST_NUMBER = r.TEST_NUMBER " & Chr(13) & "" & Chr(10) & "AND r.UNITS = u.UNIT_CODE " & Chr(13) & "" & Chr(10) & "AND ((s.START_DATE>={ts '2012-01-01 00:00:00'} And s.START_DATE<={ts '2012-10-24 23:59:59'}) " & Chr(13) & "" & Chr(10) & "" _
        , _
        "AND (t.ANALYSIS='SCAN') " & Chr(13) & "" & Chr(10) & "AND (r.NAME='" + CStr(Range("B4")) + "') " & Chr(13) & "" & Chr(10) & "AND (s.DESCRIPTION Like '" + CStr(Range("B5")) + "') " & Chr(13) & "" & Chr(10) & "AND (r.REPORTABLE='T'))" & Chr(13) & "" & Chr(10) & "ORDER BY s.TEXT_ID" _
        )
        .Name = "Query from TESTDB"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
            Columns("A:A").Select
            Selection.ColumnWidth = 18
            Columns("B:B").Select
            Selection.ColumnWidth = 20
            Columns("C:C").Select
            Selection.ColumnWidth = 70
            Range("B1").Select
    End With
Application.ScreenUpdating = True
End Sub

Moderator's Note: Put code tags on your codes. Thanks.