Errors in SELECT query from Excel to Access

[ChrisBrook]

I'm trying to produce an Excel workbook which connects to my access database and extracts values from a table based on whether those records have a Request_Date between two strings.
The code I have written works fine if I substitute the strings for actual dates however when I then try to replicate this with strings I get the above error, followed by an additional error 'Operation is not allowed when the object is closed'
Below is the code I am using:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub RetrievePaymentMI()
Dim Date1 As Date
Dim Date2 As Date
Dim LastRow As Long
Dim MIdb As String
Dim MIpass As String

Sheets("Data").Range("B5:N1000").Clear

Date1 = Sheets("Summary").Cells(3, 4).Value
Date2 = Sheets("Summary").Cells(4, 4).Value

OpenDataBase MIdb, MIpass

sSQL = "SELECT Policy_Number, Customer_Name, Request_Date, Payment_Type, Dual_Amt, Int_Amt, DnI_Amt, Resolve, Payment_Method, Payment_Description, Further_Info, TL_Name, Status " & _
"FROM tblData " & _
"WHERE Request_Date > Date1 AND Request_Date < Date2 "

RetrieveDataBaseData (sSQL)

Sheets("Data").Cells(5, 2).CopyFromRecordset RecordSet

RecordSet.Close
MIDatabaseconnection.Close

MsgBox "Complete"

End Sub</code>I've attempted numerous different ways of doing this, I've declared Date1 & Date2 as strings and dates, I've changed their names. I've attempted enclosing them in # #, " " & ' '.
HELP!!

 

[CalcSux78]

Try:
"WHERE Request_Date > " & Date1 & " AND Request_Date < " & Date2

 

[ChrisBrook]

So I now have:

sSQL = "SELECT Policy_Number, Customer_Name, Request_Date, Payment_Type, Dual_Amt, Int_Amt, DnI_Amt, Resolve, Payment_Method, Payment_Description, Further_Info, TL_Name, Status FROM tblData WHERE Request_Date > " & Date1 & " AND Request_Date < " & Date2 & ""

And it runs through with no errors but now it isn't retrieving any data from the tables.

 

[RoryA]

Access generally likes dates enclosed in # so:

sSQL = "SELECT Policy_Number, Customer_Name, Request_Date, Payment_Type, Dual_Amt, Int_Amt, DnI_Amt, Resolve, Payment_Method, Payment_Description, Further_Info, TL_Name, Status FROM tblData WHERE Request_Date > #" & Date1 & "# AND Request_Date < #" & Date & "#"

 

[ChrisBrook]

Access generally likes dates enclosed in # so:

sSQL = "SELECT Policy_Number, Customer_Name, Request_Date, Payment_Type, Dual_Amt, Int_Amt, DnI_Amt, Resolve, Payment_Method, Payment_Description, Further_Info, TL_Name, Status FROM tblData WHERE Request_Date > #" & Date1 & "# AND Request_Date < #" & Date & "#"

Brilliant, that's worked a treat! Cheers!

 

[RoryA]

Glad to help. Welcome to the forum, by the way.