VB criteria for SQL(Query) parameter driven

[aka_krakur]

I have a VB that invokes an Excel Query
the entire script works up until the parameter I need.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=DBNAME;UID=USER;PWD=PASSWORD;DBQ=DBNAME;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM" _
        ), Array("=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), _
        Destination:=Range("A4"))
        .CommandText = Array( _
"SELECT {LONG LIST OF FIELDS HERE - IRRELEVANT} " _
FROM {list of tables} " _
Where {list of joins} " _
and table.date >= " & Range("B1") " _
)
.Name = "Query from DBNAME"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

I am trying to run the above query when the user inputs a new date in cell B1.
Oracle DB is specific to need the format of the date parameter to be 'DD-MMM-YYYY'
When I remove the parameter and physically type '01-AUG-2011' AFTER the table.date >=
the VB works.
Any idea what I might be doing wrong?

 

[Gary McMaster]

Just a guess ...

... table.date >= " & Range("B1")[COLOR=Red].Text[/COLOR]

Maybe it's defaulting to .Value and passing a serial number?

Gary