Solved: Problem with Dates

[Cavaliere]

Hi everyone,

I'm trying to import data from Access table into Excel spreadsheet with the following code:


Private Sub CommandButton1_Click()
Dim dbDate As Date
dbDate = Worksheets("Sheet1").Range("Date").Value
Dim Connection As ADODB.Connection
Dim ResultSet As ADODB.Recordset

Set Connection = New ADODB.Connection
Connection.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = G:\temp\Database1.mdb;"

Set ResultSet = New ADODB.Recordset

ResultSet.Open "SELECT * FROM USDQ WHERE Date='" & dbDate & "'", Connection, , , adCmdText

Worksheets("Sheet1").Range("e2").CopyFromRecordset ResultSet

End Sub


the problem is I cannot figure out date syntax and ...WHERE Date='" & dbDate & "'",... is not working. Date formats in excel and in access are the same. Could you please guide me with it.
Thanks in advance.

[OBP]

You have to use the # symbol instead of the ' symbol.

[Cavaliere]

It's not working, I've got the same Automation error.

[OBP]

What error message do you get?

[Cavaliere]

Run-time error '-2147217913 (80040e07)':
Automation error

[OBP]

Perhaps it is not being caused by the Date, have you tried the automation without it?

[Cavaliere]

Query without filter and when I filter by other field the script works fine without any errors

[OBP]

Can you post the latest code that you used?

[Cavaliere]

Here the code


Private Sub CommandButton1_Click()
Dim dbDate As Date
dbDate = Worksheets("Sheet1").Range("Date").Value
Dim Connection As ADODB.Connection
Dim ResultSet As ADODB.Recordset
Set Connection = New ADODB.Connection
Connection.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = G:\temp\Database1.mdb;"
Set ResultSet = New ADODB.Recordset
ResultSet.Open "SELECT * FROM USDQ WHERE Date=#" & dbDate & "#", Connection, , , adCmdText
ActiveSheet.Range("e2").CopyFromRecordset ResultSet

End Sub

[OBP]

Try putting a space between the = and the # and the # and the ".
Also ensure that your date is the correct format, you might need to use
Format(dbDate, "mm/dd/yyyy")
Which is US style.

[Cavaliere]

Thanks a lot, problem solved. It turned out that whether excel or access or both are sensitive to date format and work ONLY with US style. I changed only my regional settings to US and the script worked which is odd for me.
OBP, thanks again)

[geekgirlau]

Actually both Access and Excel are very US-centric when it comes to dates, regardless of what your regional settings are. I recommend always forcing a specific date format in your code.