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.
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:
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