-
Solved: Problem with Dates
Hi everyone,
I'm trying to import data from Access table into Excel spreadsheet with the following code:
[vba]
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
[/vba]
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.
-
You have to use the # symbol instead of the ' symbol.
-
It's not working, I've got the same Automation error.
-
What error message do you get?
-
Run-time error '-2147217913 (80040e07)':
Automation error
-
Perhaps it is not being caused by the Date, have you tried the automation without it?
-
Query without filter and when I filter by other field the script works fine without any errors
-
Can you post the latest code that you used?
-
Here the code
[VBA]
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
[/VBA]
-
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.
-
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)
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules