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

We can't put a date range into a query, like:

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

dude, you're using msaccess syntax on an oracle database -- you gots to use oracle syntax

As Access is capable of performing some translation, you could try:
I don't know with Oracle, but it works with MS SQL Server (2005 and 2008 R2).

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

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

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:

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

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?

Same driver is in use for Access97 and Access2010, which uses the Oracle 11.1.0.7 client.
--=cf