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

 

[Norie]

Have you tried manually running the SQL in the database?

You can do that by printing it to the Immediate Window with this,

Debug.Print strSql

and then copying the output into query window.

By the way, why are you executing the query instead of opening it?

It's usually only action queries, eg INSERT, APPEND etc, you execute.

 

[dalibord]

I tried SQL SELECT * FROM Quality WHERE Date = #7.1.2015# directly in database and even here it does not work --> it pops out text: syntax error in date in query expression. Do you have any idea what is wrong?
My colleagues do not have Access installed on their computers, that is why I want to execute it into Excel.
Thanks for support.

 

[Blade Hunter]

I don't have a great deal off Access experience as I work with MSSQL, PL/SQL and informix databases but I have not seen the use of a # as a wrapper in a date string before.

In PL you would do something like strSql = "Select * from Quality where Date = to_date('" & dt & "','DD/MM/YYYY')"

I also wouldn't call a column "Date"

 

[dalibord]

To be honest, I am not so familiar with SQL strings. I executed your proposal and it did not work for me --> "to_date undefined function"

 

[Blade Hunter]

Try this

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

 

[dalibord]

If I use any other variable than Date, the VBA and SQL query works fine, eg. "SELECT * FROM Quality WHERE Name = '" & nm & "';" ; before nm declaration Dim nm as String...

 

[dalibord]

I tried strSql = "Select * from Quality where Date = cdate(format(" & dt & ", ""##/##/####""))" --> no success --> syntax error in query expression Date...

 

[Blade Hunter]

OK Select a row and tell me what format the date looks to be in.

Then we can adjust the query to emulate that. This is the difficulty with matching dates on a DB I have found.

 

[dalibord]

In Acess DB it has data type Date/time, format as short date (= dd.mm.yyyy). In VBA dim dt is declared as Date.