Board index » delphi » MS Access Date SQL query problem
Bob Dalton
 Delphi Developer |
MS Access Date SQL query problem2004-10-16 05:05:02 AM delphi199 I am converting an existing MS SQL 2000 program to work * also * with Access Jet database. Essentially I am providing both SQL databases and Jet databases and the users can decide based on what he/she has which database they want to use with my client program. This statement worked fine in MS SQL: FiltCrit := 'select * from Schedule where SCH_ENDTIME < ' + QuotedStr(DateToStr(Date)); FiltCrit := FiltCrit + ' order by SCH_STARTTIME'; ScheduleDB.CommandText := FiltCrit; When the same query is used with the Microsoft.Jet.OLEDB.4.0 provider I get an error message that states the criteria is not acceptable. I changed it to the following which Jet accepted: FiltCrit := 'select * from Schedule where SCH_ENDTIME < ' + DateToStr(Date); FiltCrit := FiltCrit + ' order by SCH_STARTTIME'; ScheduleDB.CommandText := FiltCrit; The trouble is that while it is apparently acceptable it produces no results even though the table has data that meets the criteria above to display. I tried this as well: FiltCrit := 'select * from Schedule where SCH_ENDTIME < #' + DateToStr(Date) + '#'; FiltCrit := FiltCrit + ' order by SCH_STARTTIME'; ScheduleDB.CommandText := FiltCrit; This worked but will not work in MS SQL program and the program must be able to service both providers. As an experiment I also tried to set the options (under Tables/queries) for the Jet database to be compatible with ANSI92 SQL Server syntax but still didn't work. Being relatively new to MS Access it probable I am missing something obvious here but so far it eludes me. Any help would be appreciated. I need the syntax to be acceptable to BOTH MS SQL Serevr and MS Access (Jet). for dates. BTW SCH_ENDTIME is a date/time field. Regards; Bob Dalton |