Vba sql query date type error

[dalibord]

Hi all,
I have a problem to get the data into Excel Sheet from Acess database when using Date as a variable. Do not know if I have correct SQL syntax when Date is variable. When I tried '" & dt & "', I got data type mismatch error. For #" & dt & "# which is in the code below, I got syntax error instead.
Thanks for support.

Sub DowloadDate()
Dim cn As Object
Dim i As Integer
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim dt As Date
dt = Range("G2").Value
Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=\Database.accdb"
strSql = "SELECT * FROM Quality WHERE Date = #" & dt & "# ;"


cn.Open strConnection
Set rs = cn.Execute(strSql)
'Copy table headers
For i = 0 To rs.Fields.Count - 1
Sheets("Summary").Cells(1, i + 1) = rs.Fields(i).Name
Next i
'copy database values
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

 

[Blade Hunter]

strSql = "Select * from Quality where Date = cdate(format(" & dt & ", ""##.##.####""))"

OK, try this.

Or maybe even this?

strSql = "Select * from Quality where Date = cdate(format(" & dt & ", ""DD.MM.YYYY""))"

I am only guessing at this based on what i found in a google search though so it may or may not work.

 

[Blade Hunter]

Also, humour me here and try changing Date to [Date]

You may not even need to format it, it might just be strSql = "Select * from Quality where [Date] = " & dt

 

[Norie]

You shouldn't have a field named Date, Date is a VBA function that is used in Access.

If you do have a field named Date you should change it, but in the meantime you can try enclosing it in [ ].

You do need to have # around the date in Access, but I'm not sure 7.1.2015 is a valid date format.

 

[dalibord]

Both proposals with the same result - syntax error. I tried to change column name Date in Acess and get the result in Excell: data type mismatch criteria expression..