A surprise when querying Time using Access (2007) from MS SQL Server (2005) table via ODBC.
SQL Server DateTime field. Access Zero Date (12/30/1899) used as the date component so Access automatically shows the time only in the linked table.
This field cannot be sensibly queried by Access using comparative operators (>, =, < , Between etc).
Equating to a time in a query finds no matching record. Bewilderingly, Less-Than and Greater-Than queries actually return the opposite of what they should.
Including 12/30/1899 in the query criteria is discarded as it is everywhere in Access.
The issue appears to be the interpretation of the zero date in the records by Access. The date in the criteria of the Access query is treated correctly as 12/30/1899 if the date in the records any other date.
The comparison must be made using DateDiff even though the comparative operators work fine on other dates from SQL Server and inside Access tables.
DAO recordset queries respond similarly. I am about to try using ADO.
(BTW The Zero Date in SQL Server is 1/1/1900 but that isn't important here.)
SQL Server DateTime field. Access Zero Date (12/30/1899) used as the date component so Access automatically shows the time only in the linked table.
This field cannot be sensibly queried by Access using comparative operators (>, =, < , Between etc).
Equating to a time in a query finds no matching record. Bewilderingly, Less-Than and Greater-Than queries actually return the opposite of what they should.

Including 12/30/1899 in the query criteria is discarded as it is everywhere in Access.
The issue appears to be the interpretation of the zero date in the records by Access. The date in the criteria of the Access query is treated correctly as 12/30/1899 if the date in the records any other date.
The comparison must be made using DateDiff even though the comparative operators work fine on other dates from SQL Server and inside Access tables.
DAO recordset queries respond similarly. I am about to try using ADO.
(BTW The Zero Date in SQL Server is 1/1/1900 but that isn't important here.)