asked on

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?

Scott McDaniel (EE MVE )

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 Wilson

ASKER

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:

Scott McDaniel (EE MVE )

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?

Dale Fye

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 Wilson

ASKER

It is reporting Date/Time.

Kerry Wilson

ASKER

We are using the MSDE 2000 SQL (Free version).

Kerry Wilson

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial