Access 2007 ODBC Date/Time filtering

Kerry Wilson
Kerry Wilson used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 Administrator

Author

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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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 Administrator

Author

Commented:
It is reporting Date/Time.
Kerry WilsonNetwork Administrator

Author

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

ODBC Configuration
Kerry WilsonNetwork Administrator

Author

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.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial