Access SQL & Dates...

[TBreak]

I am trying to select portion of a dataset using a TADOQuery with a date filter, but no matter what format I use it seems to make no difference. eg.

SELECT * FROM DISBURSEMENTS WHERE "DATE CREATED = #21/07/03#"

I have tried it with all the date formats possible (dd-mm-yy, yy-mm-dd etc...) and with dash seperators and slash, but it just seems to ignore the statement and return all the records in the table.

I have checked the MSDN site, here and several others but I cannot find a reason/way of resolving this. Can anyone out there enlighten me?

TIA.

 

[lespaul]

Don't let your melting brain get into the keyboard, god knows what kind of trouble you'd have then (and think of the mess)!!!

Good luck!

Leslie

 

[DoorV]

CDate() is a Type Conversion Function in Visual Basic I came across when programming an Access database, buggy and never worked right (or more likely didnt understand it properly) and had to create a work around, one of the reasons I changed to Delphi

 

[TimSNL]

I don't think you need any quotes at all!

SELECT * FROM DISBURSEMENTS WHERE Table1.[DATE CREATED]=#07/10/2003#

 

[petertickler]

OK, lets try to put this to bed.

I have created a test table (SQL Server 7) with an ID field and a field called 'Date Created'.

I added a few records.

I then ran the following piece of code:

with dmDBT.quDateTest do begin
if active then close;
sql.clear;
SQL.add('select * from Datetest where [Date Created] < ''07/20/03''');
open;
showmessage(IntToStr(RecordCount) + ' records found');
end;

It worked perfectly.

Please note that I have to pass the date in the format mm/dd/yy even though we use using English dates (dd/mm/yy) throughout our network.

Date Created is in square brackets.

The date is passed in single quotation marks - but because the SQL statement is enclosed in single quotation marks, the single quotation mark either side of the date needs to be preceded by another single quotation mark (i.e. there are NO double quotation marks despite what it looks like to the naked eye).

I hope this works for you!