Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: "between #1/1/2001# and #12/31/2001#" doesn't work

    We're finally moving from Access97 to Access2010, and something that used to work, now throws an:

    Code:
    "ODBC call failed. [Oracle][ODBC] Syntax error or access violation. (#0)"
    We can't put a date range into a query, like:

    Code:
    between #1/1/2001# and #12/31/2001#
    or really, any kind of date comparison (like "> #1/1/2001#") without generating that error. If I set up the dates as parameters and get prompted for them at runtime, everything runs as expected.

    Did this functionality get de-supported in Access2010?

    Thanks,
    --=Chuck

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, you're using msaccess syntax on an oracle database -- you gots to use oracle syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As Access is capable of performing some translation, you could try:
    Code:
    "between " & #1/1/2001# & " and " & #12/31/2001#
    I don't know with Oracle, but it works with MS SQL Server (2005 and 2008 R2).
    Have a nice day!

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    But I'm just typing this into the Query Editor, into the Criteria section, rather than building a string in VBA. So, Access2010 auto-populates my between statement with "#" around the dates. Plus, this same syntax works in Access97.

    Also, we're using ODBC to connect to Oracle, and the same DSN for both Access97 and Access2010, and so this syntax is already being translated on my behalf by the driver, but only in Access97.

    --=cf

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just for fun, type in '2001-01-01' and '2001-12-31' using single quotes instead of octothorps and in yyyy-mm-dd format in case oracle has problems with american dates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    Between '2001-01-01' And '2001-12-31'
    Between '01-JAN-2001' And '31-DEC-2001'
    All result in "Data type mismatch in query expression" (which is not the same error as above).

    But parameters, and any expression which uses MSAccess functions which cast strings as dates, will work. Like the following, which definitely is not Oracle syntax, but works:

    Code:
    between CDate("1/01/2001") And CDate("01/31/2001")
    I'm ok with teaching people CDate(), but we're talking hundreds of non-IT users who have learned to use octothorps (cool word) in their date expressions. I'd rather get those working again ... but if I can't, I can't

    --=Chuck

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I tested with Access 2003 and Access 2010 and it works in both cases. Maybe it's something due to the Oracle driver. Did you tested the solution proposed by r937?
    Have a nice day!

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Same driver is in use for Access97 and Access2010, which uses the Oracle 11.1.0.7 client.
    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •