Excel Help for Trouble With Vba Query Using Date With Odbc

I am just starting to learn VBA and trying to get data from an ODBC database using a select with a date parameter. This following statement was created by using microsoft query and it works fine:

VBA Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=hist;UID=OSI;PWD=OSIOSI;DBQ=HIST;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT DATA_VALUES_5MIN_12_2009.TIME, DATA_VALUES_5MIN_12_2009.OSI_KEY, DATA_VALUES_5MIN_12_2009.DATA_TYPE, DATA_VALUES_5MIN_12_2009.VALUE" & Chr(13) & "" & Chr(10) & "FROM OSI.DATA_VALUES_5MIN_12_2009 DATA_VALUES_5MIN_12_2009" & Chr(13) & "" & Chr(10) & "" _
        , _
        "WHERE (DATA_VALUES_5MIN_12_2009.DATA_TYPE=1)and (DATA_VALUES_5MIN_12_2009.TIME>{ts '2009-12-08 00:00:00'}) AND (DATA_VALUES_5MIN_12_2009.OSI_KEY='03057174')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_hist"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I am trying to make the date a variable and modified the query as shown:

VBA Code:

Sub Macro1()
'
' Macro1 Macro
'
Dim datepick As Date
datepick = Range("L1").Value = Format("mm/dd/yyyy hh:mm:ss am/pm")

Debug.Print datepick

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=hist;UID=OSI;PWD=OSIOSI;DBQ=HIST;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT DATA_VALUES_5MIN_12_2009.TIME, DATA_VALUES_5MIN_12_2009.OSI_KEY, DATA_VALUES_5MIN_12_2009.DATA_TYPE," _
        , _
        "DATA_VALUES_5MIN_12_2009.VALUE" & Chr(13) & "" & Chr(10) & "FROM OSI.DATA_VALUES_5MIN_12_2009 DATA_VALUES_5MIN_12_2009" & Chr(13) & "" & Chr(10) & "" _
        , _
        "WHERE (DATA_VALUES_5MIN_12_2009.DATA_TYPE=1) and (DATA_VALUES_5MIN_12_2009.TIME> datepick)" _
        , _
        "AND (DATA_VALUES_5MIN_12_2009.OSI_KEY='03057174')" _
        )

Debug.Print datepick

        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_hist"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Can somebody provide some solution to this problem?
Thanks

Also, once i get the data that i need i will need to create a pivot table to show the data that i need. Can i use the transform function instead?

Trouble with VBA query using date with ODBC

Excel Help for Trouble with VBA query using date with ODBC in Automating Data Analysis Tasks