ODBC SQL Server Date queries

[SpentGeezer]

Greetings,

I have an access database which has ODBC linked SQL Server tables. When I try to query any date fields it causes Access to crash.

E.g:
SELECT tbl_maintenance.d_CommDeadline, tbl_maintenance.d_Issued, tbl_maintenance.OBJECTID, tbl_maintenance.txt_SegmentID, tbl_maintenance.txt_Code, tbl_maintenance.txt_AuspecDesc, tbl_maintenance.txt_RoadScore, tbl_maintenance.txt_MaxTime, tbl_maintenance.DEFECT_ID, tbl_maintenance.txt_HouseNo, tbl_maintenance.txt_StreetName, tbl_maintenance.num_Ward, tbl_maintenance.txt_Suburb, tbl_maintenance.txt_StreetClass, tbl_maintenance.txt_Location, tbl_maintenance.txt_WorksReqNo, tbl_maintenance.d_Recieved, tbl_maintenance.d_Inspected, tbl_maintenance.d_WrksCompleted
FROM tbl_maintenance
WHERE (((tbl_maintenance.d_CommDeadline)<#9/2/2010#) AND ((tbl_maintenance.d_Issued) Is Not Null) AND ((tbl_maintenance.d_WrksCompleted) Is Null));

"(((tbl_maintenance.d_CommDeadline)<#9/2/2010#)" causes it to crash. If this is removed their is no crashing.

Does anyone know how to query SQL server tables for date? BTW if I copy the table in a s a local table, the query works fine, so it has something to do with SQL Server.

Cheers

 

[boblarson]

You can try surrounding your date with single quotes instead of octothorpes. I just tested with my linked ODBC table and it worked fine with octothorpes, but who knows.

 

[SpentGeezer]

You can try surrounding your date with single quotes instead of octothorpes. I just tested with my linked ODBC table and it worked fine with octothorpes, but who knows.

Single quotes = Data Type mismatch
Removing octothorpes = Access just puts them in .... hang on, if I do it in SQL mode without the ## then it works.

My apologies for my ignorance......