Access 2007 ODBC Date/Time filtering

We are using Access 2007 as an interface to a SQL Database via ODBC connections.  We have observed we are not able to apply filters to Date/Time columns in the tables.  The sort works fine...just not filtering.  A query that selects a date range works correctly as well.

These same tables filtered just fine with Access 2000.

Any ideas?
Kerry WilsonNetwork AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you applying the filters?

What do you mean by "not able to apply filters"? Do you get an error? Or do you get data, but not the data you expect?
Kerry WilsonNetwork AdministratorAuthor Commented:
The Data Type in the SQL Database is "smalldatetime" so the error message does not make sense when it talks about a character string.

Here is the error message:

Error message after right-clicking an item in the date column and selecting the date filter.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Open the linked table in design view (Access will complain, but will allow you to do it). What Datatype is Access reporting for the column?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dale FyeOwner, Dev-Soln LLCCommented:
What SQL Server driver are you using for your connection string?

Try the "SQL Server Native Client 11.0" if you are using SQL Server 2008
Kerry WilsonNetwork AdministratorAuthor Commented:
It is reporting Date/Time.
Kerry WilsonNetwork AdministratorAuthor Commented:
We are using the MSDE 2000 SQL (Free version).

ODBC Configuration
Kerry WilsonNetwork AdministratorAuthor Commented:
Well, everyone got quiet when after I posted my last message about SQL 2000.  I know, I know.  I've been trying to get my boss to upgrade our systems.

Is there a chance if I download the new drivers that they will properly interface with the SQL engine?  I'm willing to give it a try.  But, I don't want to do that if there's no chance it will work or, more importantly, if it will corrupt something to the point of making things worse.
Gustav BrockCIOCommented:
If the field is linked as Date/time, it should work. Now, you don't tell which filter you input, and an incorrect or incomplete filter expression may cause exactly this error:

SQL Server Error Messages - Msg 241

However, the error is much more likely caused by the use of Smalldatetime. No matter what, I would modify that at the soonest to Datetime which matches much better data type Date of Access.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.