Consulting

Results 1 to 12 of 12

Thread: Solved: Problem with Dates

  1. #1

    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.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You have to use the # symbol instead of the ' symbol.

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

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What error message do you get?

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

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Perhaps it is not being caused by the Date, have you tried the automation without it?

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

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Can you post the latest code that you used?

  9. #9
    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]

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  11. #11
    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)

  12. #12
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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
  •