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.

 

[jbpelletier]

just a guess.. can't it be there?

SELECT * FROM DISBURSEMENTS WHERE [white]"[black]DATE CREATED = #21/07/03#[white]"[black]

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

jb

 

[Zathras]

Is the column name really "DATE CREATED" (with the space)? In that case, try

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

or

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

depending on your local date format.

 

[petertickler]

It depends on your database and date format.

I am using TADOQuery with SQL Server 7 database. I find that it works if the dates passed are inside single quotation marks, and if the dates are in the format
MM/DD/YY (even though I dispLay dateS as DD/MM/YY on my system!

So a piece of my SQL would look like:

SELECT * FROM MYTABLE WHERE CREATIONDATE > '19/07/03'

 

[TBreak]

Thx guys, but I have tried all those combos. Yes the name has a space in it, and I tried the square brackets and in quotes. The SQL command is not rejected when executed, which it was when the name was incorrectly spelt.

I am really at a loss. There is data in the table that matches the data, and I am just testing it in a very basic test rig, with an ADOConnection, ADOTable, ADOQuery and a DBTable. I have added a TEdit so I can keep trying multiple variations eaily, but no results come back.

Someone out there MUST have had to create data extracts on an Access table using a date criteria! HELP! ;-)

Any other ideas?

TIA

 

[DoorV]

Hi I had a problem with SQL dates and found that the date was in american format mm/dd/yyyy so i cheated and formated my search string to fit that after it was entered by the user.. not a very elegant fix but it worked.

 

[TBreak]

Thx, Just tried that, but it appears not to work with Access. I am getting a "Data type mismatch in criteria expression" error.

This is wierd. I do:

SELECT * FROM DISBURSEMENTS WHERE "[DATE CREATED] = 07-21- 03" and get all the records in the dataset.

If I do

SELECT * FROM DISBURSEMENTS WHERE [DATE CREATED] = "07-21- 03"
or
SELECT * FROM DISBURSEMENTS WHERE [DATE CREATED] = "#07-
21-03#"

I get the error above... It also appies if I use a slash instead of a dash in the date. I have tried all combos again (mm/yy/dd, dd/mm/yy, yy/mm/dd etc...) and the error applies when specified the the formats shown above.

N e more ppl? Remember I am accessing an Access 2002 DB backend.

TIA.

 

[EricDraven]

I have also had problems extracting fields but I suspect that it is more to do with the field name than the fact it is of a date type!

Tyr this method, it works fine for reserved words in my programs.

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

When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[DoorV]

I dont know if this helps and I am definitely not an expert user but I used this to select cases from a database

s:='WHERE (([Tbl Demographics].Forename)="'+Forename.text+'" ';
s:=s+' OR ([Tbl Demographics].Surname)="'+surname.text+'"';
s:=s+' OR ([Tbl Demographics].HospNo)="'+hospno.text+'"';
d:=maskedit1.Text;
try
md:=strtodate(maskedit1.Text);
except
dateflag:=false;
end;
if dateflag=true then
begin
d:=reversedate(maskedit1.Text); (* this changes date to american format *)
s:=s+' OR ([Tbl Demographics].DOB)=#'+d+'#';
end;
s:=s+');';
Addnewform.namesearch.Close;
Addnewform.namesearch.SQL.Clear;
Addnewform.namesearch.SQL.Add('SELECT [Tbl Demographics].Studyno, [Tbl Demographics].Forename, [Tbl Demographics].Surname, [Tbl Demographics].DOB, [Tbl Demographics].HospNo');
Addnewform.namesearch.SQL.Add('FROM [Tbl Demographics]');
Addnewform.namesearch.SQL.Add(s);
Addnewform.namesearch.Open;

It does work after a fashion and there are probably better ways to do it.

 

[petertickler]

Is your date being stored as a datetime field? If so, and the time stored not = 00:00, then that might be a reason for no records being returned if you are using an &quot;=&quot; operator. Try a &quot;>&quot; or &quot;<&quot; operator.

The only other thing I can suggest is that you publish again the SQL you have written which runs, but fails to return any results.

 

[TBreak]

Thx again, guys.

Eric> I did that as the 1st line of attack (see above).

DoorV> I am using a straight TEdit to enter a qeury string directly in as I am just testing b4 I integrate the functionality fully into the app.

Just to recap:

I have tried all the formats I am aware of, including using [] around the space seperated field name, I have tried with just &quot;&quot; around the date and with &quot;#dd/mm/yy#&quot; format and all plausible variations of date format in each of the above parenthesis combos. I have also tried putting the whole search criteria in &quot;&quot; as well as just the field name and the criteria, eg. &quot;DATE CREATED&quot; = &quot;#21/07/03#&quot;.

For some types of attempt I get an error regarding Data type mismatch (as explained above.

Damn it. There must b an answer out there somewhere. I bet VB programmers don't get these issues... ;-(

 

[lespaul]

I think you will need the table name in front of the field, like Eric said, but with this format instead:

Table.&quot;Date Created&quot; = '#02/20/1993#'

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[TBreak]

Ok, tried that too. No good. I get no errors, but no results. I tried:

SELECT * FROM DISBURSEMENTS WHERE &quot;DISBURSEMENTS.DATE CREATED&quot; = &quot;#03/07/21#&quot;

SELECT * FROM DISBURSEMENTS WHERE &quot;DISBURSEMENTS.DATE CREATED&quot; = &quot;#21/07/03#&quot;

SELECT * FROM DISBURSEMENTS WHERE &quot;DISBURSEMENTS.DATE CREATED&quot; = &quot;#07/21/03#&quot;

SELECT * FROM DISBURSEMENTS WHERE &quot;[DISBURSEMENTS.DATE CREATED]&quot; = &quot;#03/07/21#&quot; (and the same date combos as above)

As the table name is stated in the FROM clause it should not be necessary, but I tried it n e way. Still no result.

Anymore ideas? This is getting me down!

 

[DoorV]

All i can think of is that there may be something wrong with the the DATE CREATED field that your checking, if you get no error messages it would suggest that the match is okay but in some way it isnt being represented the way you expect.

 

[Zathras]

Leaving Delphi alone for a bit and working exclusively in Access, can you create a query to get a display of the results you want?

If not, then you may have a data problem.

If so, then look at the generated SQL and copy/paste directly into a TADOQuery.SQL.Text and see if you can get the same results from Delphi.

 

[lespaul]

But that's NOT what it said to do! Here's your code modified:

SELECT * FROM DISBURSEMENTS WHERE DISBURSEMENTS.&quot;DATE CREATED&quot; = '#03/07/21#'




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[TBreak]

LESLIE> It does not work. Using the query above provides an error:

&quot;Invalid use of '.', '!', or '()' in query expression 'DISBURSEMENTS.&quot;DATE CREATED&quot; = '#03/07/21#'&quot;

The reason I modified the above code was I figured you had typoed. ALSO, the use of '' around the date creates an error, which I also tested using &quot;&quot; and multiple date formats, slashes, dashes and with/without #.

I never just try 1 permutation from advise, I try multiple formats and structures, so as to prevent asking questions about basic variations on a theme.

Thanks tho.

 

[lespaul]

What happens when you use single quotes around your date?

(copied from 7th posting in thread):

SELECT * FROM DISBURSEMENTS WHERE [DATE CREATED] = '#07-21- 03#'




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[TBreak]

There could be an issue with the Access version. When I use my Access 2000 to open the table there is a prob with the date field when I try to set the default value, saying a function has not been installed CDate() (whatever that is). So I will try to update to Access 2002 tomorrow and see if that helps the situation. I did try changing the fieldname to DATECREATED but that didn't help, so the space in the name does not appear to be an issue.

I'll let you all know. Thanks for all the help so far guys!

 

[TBreak]

Leslie> When I do that I get the error:

&quot;Data type mismatch in criteria expression&quot;

I will try a normal Access 2000 database tonight with a date field and see what difference that makes and give you an update tomorrow.

I feel like my brain is slowly melting outta my ears, nose etc...