runtime error 1004 general odbc error

Hi,

Below code works fine if I enter a specific date in the code, but as soon as Í try to refer to a cell in my excel sheet i get 'runtime error 1004 general odbc error'. The user "credit_read" to the database is a common user.

Code not working:

Public Sub WWR()
Dim DateFrom As String
Dim DateTo As String

DateFrom = Worksheets("Sheet3").Range("comp_date").Text
DateTo = Worksheets("Sheet3").Range("report_date").Text

Range("a6:e11").Select
Selection.Delete Shift:=xlUp



    Range("a6:e11").Select
    Selection.Delete Shift:=xlUp

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;Driver={Oracle in OraHome11gR2_32Bit_1};Dbq=TWP;Uid=CREDIT_READ;" _
        )), Destination:=Range("$A$6")).QueryTable
        .CommandText = Array( _
        "select  isin.instrument_name stock_name, " & Chr(13) & "" & Chr(10) & _
        "round(deals.market_value/1000000,1) market_value,  " & Chr(13) & "" & Chr(10) & _
        "round((deals.market_value-deals_1.market_value)/1000000,1) change," & Chr(13) & "" & Chr(10) & _
        "round(deals.notional1/1000000,1) notional,", "" & Chr(13) & "" & Chr(10) & _
        "deals.end_date maturity_date" & Chr(13) & "" & Chr(10) & _
        "from ccrd.com_deals deals" & Chr(13) & "" & Chr(10) & _
        "left join glostatic.gs_counterpart cp on (deals.counterpart_su_key = cp.su_key and cp.is_group_internal = 'N')" & Chr(13) & "" & Chr(10) & _
        "left join ts.ts_trade_leg ts on ", "deals.trade_su_key = ts.trade_su_key" & Chr(13) & "" & Chr(10) & _
        "left join mcdm.in_instrument isin on ts.effect_su_key = isin.su_key" & Chr(13) & "" & Chr(10) & _
        "left join ts.ts_trade bs on bs.su_key = ts.trade_su_key" & Chr(13) & "" & Chr(10) & _
        "left join ccrd.com_deals deals_1 on (", "deals.trade_su_key = deals_1.trade_su_key and deals_1.eod_date = '30mar12')" & Chr(13) & "" & Chr(10) & _
        "where deals.eod_date = '30apr12'" & Chr(13) & "" & Chr(10) & _        
        "and deals.product_code like '%Equity Swap%'" & Chr(13) & "" & Chr(10) & _
        "and substr(isin.instrument_name,1,5) = substr(cp.name, 1,5)" & Chr(13) & "" & Chr(10) & _
        "order by deals.market_value desc")
        .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_twp"
        .Refresh BackgroundQuery:=False
    End With
    
    ActiveSheet.ListObjects("Table_Query_from_twp").Unlist
    
    Range("E7:E11").Select
    Selection.NumberFormat = "d-mmm-yy"
    Range("A1").Select
    ActiveWorkbook.Save

If the two lines referring to dates in the excel sheet is replaced by the below it works:

"left join ccrd.com_deals deals_1 on (", "deals.trade_su_key = deals_1.trade_su_key and deals_1.eod_date = '30mar12')" & Chr(13) & "" & Chr(10) & _
"where deals.eod_date = '30apr12'" & Chr(13) & "" & Chr(10) & _

WHY??

Thank you for you time.

Br,
Hanne Marit