SQL parameter query in VBA (working with dates)

[bartekR]

It's my first visit on this forum so hello to Everyone.

I have a problem with the below code. I am trying to import recordset into excel with the option to specify parameter value each time the records are imported.
The code works when i use numeric or text parameters the problems start when i work with date type of variables.
MAcro does not return an error however it does not return records either. The only thing that appears is a header of the report. When i dble click on it and go to microsoft query view I can see all the records displayed by specified parameter date. Why don't they get imported to excel then?
I am a bit new to SQL (and not so experienced in VBA either) therefore the answer might be obvious however i would still appreciate your help.

Here's the offending code :

Sub Macro2()

Dim strConn As String
Dim strSql As String
Dim strPar As String 'Declaring it as date doesn't help
Dim oQt As QueryTable

strConn = "ODBC;DBQ=Z:\DBASE.xls;"
strConn = strConn & "DefaultDir=Z:\;"
strConn = strConn & "Driver={Driver do Microsoft Excel(*.xls)};"
strConn = strConn & "DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

strPar = InputBox(, , , Format(Date, "mm/dd/yyyy"))

strSql = "SELECT BANKDETAILS.ID, BANKDETAILS.`NAME `, BANKDETAILS.SURNAME, BANKDETAILS.`A/C`, BANKDETAILS.`SORT CODE`, BATCH.DATE "
strSql = strSql & "FROM {oj `Z:\DBASE`.BATCH BATCH LEFT OUTER JOIN `Z:\DBASE`.BANKDETAILS BANKDETAILS ON BATCH.ID = BANKDETAILS.ID} "
strSql = strSql & "WHERE BATCH.DATE=" & strPar

Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=strConn, _
Destination:=Range("a2"), _
sql:=strSql)

oQt.Refresh

End Sub

 

[SkipVought]

You must CONVERT you mm/dd/yyyy STRING to what dBase will consider a date.

In Access it's

"WHERE BATCH.DATE=#" & strPar & "# "

in Oracle its 
"WHERE BATCH.DATE=TO_DATE(='" & strPar & "' ,'mm/dd/yyyy')"

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}