type mismatch error in SQL query in excel vba

[Vijay121]

Hi all,
I am getting error 'Type Mismatch' while copying data from access to excel sheet after giving Date criteria in SQL query.
I have users data in Access table containing Processed date field also.
I have two cells in excel sheet namely "From Date" and "To Date".
When I put dates in both cells then I should get data from access table in given range dates.
e.g. If I insert 9/25/2011(mm/dd/yyyy) in "From Date" cell and 10/9/2011(mm/dd/yyyy) in "To Date" cell then I should get data from access
into excel sheet in given dates only which is not happening.
Here is main part of the code:
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim StartDate, EndDate
StartDate = Trim(ThisWorkbook.Sheets("Reports").Range("G16").Value)
EndDate = Trim(ThisWorkbook.Sheets("Reports").Range("J16").Value)
'StartDate = Trim(ThisWorkbook.Sheets("Reports").TextBox1.Text)
'EndDate = Trim(ThisWorkbook.Sheets("Reports").TextBox2.Text)
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Vijay\Testing_DB.accdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
sSQL = "SELECT SaveDataInDB.* FROM SaveDataInDB WHERE (((SaveDataInDB.[Processed Date]) between '" & StartDate & "' and '" & EndDate & "'));"
'sSQL = "SELECT SaveDataInDB.* FROM SaveDataInDB WHERE (((SaveDataInDB.[Processed Date])>='" & StartDate & "') AND ((SaveDataInDB.[Processed Date])<='" & EndDate & "'));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cn
ThisWorkbook.Sheets("User_Data").Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

>>>>
Note: I have Processed Date field as Date with format Short Date in access database

 

[dk]

Hi

Dates in Access need to be enclosed with # symbols e.g.

sSQL = "SELECT SaveDataInDB.* FROM SaveDataInDB WHERE (((SaveDataInDB.[Processed Date]) between #" & StartDate & "# and #" & EndDate & "#));"



HTH
DK

 

[Vijay121]

Hi DK,

Thanks a lot.. Its working ....

You are the star!!!


Vijay