Querying Time range on linked table does not work?

[arichins]

I have a linked table in my access accdb file to a view on a SQL Server DB.

One of the columns is a date column, where only the time value is important, so it's stored in the default format, like 12-30-1899 12:00 AM.

In SQL Server, I can query records that fall between a certain time frame. It works in Access when written as an ADO query (that's another story), and it also works when I convert the view to a local access table.

But when I query it as a linked server, it will return everything is greater than 12-29-1899 11:59 pm, but when I try to search any date/time ranges on 12-30-1899 nothing is returned. This seems to be a bug in Access.

The reason I no longer use ADO, is that the results would not print. For some reason, when I go to print preview, Access would evaluate the query I'm passing through to SQL Server and throw a syntax error. I miss ADP. :banghead:

 

[Galaxiom]

I had the same experience. Greater Than and Less Than return back to front results for records on the zero day.

http://www.access-programmers.co.uk/forums/showthread.php?t=223279

The solution was to use DateDiff in the query.

 

[arichins]

Good to know I'm no the only one having this problem:

In my case though, I'm trying to return records whose time falls between time A and time B, which are times supplied by the Access end user. How are you using datediff?

 

[arichins]

Ok, I figured out what you meant, in the where clause, I determine if the difference between the time in the database and the user parameter is greater than or less than 0, to determine of is less than or greater than!

Nice job! I don't know I'd ever have thought to try that.