Please help me - Filter Data in DELPHI 7

[maniakul]

....
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:='SELECT * FROM Total1 WHERE Data BETWEEN '+Quotedstr(datetostr(DTPicker1.Date))+ ' AND ' + Quotedstr(datetostr(DTPicker2.Date));
ADOQuery1.Prepared := True;
ADOQuery1.ExecSQL;
ADOQuery1.Open;
ADOQuery1.Active := False;
if prReport2.PrepareReport then
prReport2.PreviewPreparedReport(true);
Label1.Caption := Quotedstr(datetostr(DTPicker2.Date));
end;

end.

Please help me, mesage error is: "Data type mismach in criteria expression" .
How is posible to solve this problem?!

 

[ChrisGSchultz]

Have you checked that the Dates in your QuotedStr are the same format as your system dates?
I use the following in my parameters to keep them independent of the operating system setup (we Antipodeans prefer the dd/mm/yyyy format for example ):

In the SQL.Text

SELECT first_name, last_name, birthdate, FROM (PBIO)
WHERE ((ucase(last_name) LIKE ucase(:last_name)+'%') 
                      AND 
                     (UCase(first_name) like UCase(:first_name)+'%')
                     AND
                     (birthdate BETWEEN [b]:dob_s[/b] and [b]:dob_e[/b]))
ORDER BY (ucase(last_name) + ucase(first_name));

Note the leading : is essential when using parameters

I've got the two date parameters [tt]dob_s[/tt] and [tt]dob_e[/tt] set up as [tt]DataType: ftDateTime[/tt]
Then in the code before I perform the query:

  with ADOqry_Patient do begin
    Close;
      Parameters.ParamByName('last_name').Value := uppercase(trim(E_LastName.Text));
      Parameters.ParamByName('first_name').Value := uppercase(trim(E_firstName.Text));

      val(SE_year.Text,nYear,eCode);  // the user chosen year
        Parameters.ParamByName('dob_s').Value := encodedate(nYear - 1,1,1);  // start DOB
        Parameters.ParamByName('dob_e').value := encodedate(nYear + 1,12,31); // end DOB
    Prepared := TRUE;
    Open;
  end; // with

Hope thats of some help

Cheers,
Chris

 

[maniakul]

No, please , how extract one period from .mdb queries for report?
Ex. period DTPicker1 =01/01/2005 to DTPicker2= 05/07/2005.
How use BETWEEN in DELPHI?
Thanks

 

[ChrisGSchultz]

Its really just a matter of using your DTPicker.Date as the parameter values rather than the encode date. The rest of the SQL is virtually the same as you've got. Here's another way (with a bit of code tidying):

with ADOquery1 do begin
  Close;
  SQL.Clear;
  // Create the parameters and load their values from the Date Pickers
[b][red]  Params.CreateParam(ftDate, 'Date1', ptInput) do
          AsDate := DTPicker1.Date; 
  Params.CreateParam(ftDate, 'Date2', ptInput) do
          AsDate := DTPicker2.Date;[/red][/b]
// Use the created parameters in the SQL statement - use [B]ADD[/B] rather than Text - as the Text is cleared!
  SQL.Add:='SELECT * FROM Total1 WHERE Data BETWEEN (BETWEEN [b][red]:Date1[/red][/b]) and [b][red]:Date2[/red][/b])';
  Prepared := True;
  ExecSQL;
  Open;
  Active := False;
     if prReport2.PrepareReport then
        prReport2.PreviewPreparedReport(true);
  Label1.Caption := Quotedstr(datetostr(DTPicker2.Date));
end; // with ADOQuery1

And quoting the Delphi help file on ADOquery:
[tt][blue]
Note: The library does not evaluate the SQL sent to the database via a TADOQuery component. The SQL used must be valid for the particular database type accessed via ADO. Any error messages passed back to the application will have originated at the ADO or database level, and so will have error codes and messages specific to those systems.
[/blue][/tt]

Hope thats helpful....

Cheers,
Chris

 

[ChrisGSchultz]

Oops - just noticed an extra bracket and extra BETWEEN in the SQL statement. It should read:

SQL.Add:='SELECT * FROM Total1 WHERE (Data BETWEEN :Date1 and :Date2)';

BTW, I assume that [tt][red]Data[/red][/tt] is a date variable. If not, you'll have to ensure that the variable your testing with the [tt][blue]WHERE[/blue][/tt] is the same type as the [tt][blue]BETWEEN[/blue][/tt] values - that can give you a data type mismatch!

Cheers,
Chris

 

[maniakul]

Many thanks, is cool!

 

[maniakul]

All ok, i have solve

...
ADOQuery1.Active := False;
ADOQuery1.SQL.Text:='';
ADOQuery1.SQL.Text :='SELECT * FROM Total1 WHERE Data BETWEEN :First and :Second';
ADOQuery1.Parameters.ParamByName('First').DataType:= ftdate;
ADOQuery1.Parameters.ParamByName('Second').DataType:= ftdate;
ADOQuery1.Parameters.ParamByName('First').value:= datetostr(DTPicker1.Date);
ADOQuery1.Parameters.ParamByName('Second').value:= datetostr(DTPicker2.Date) ;
ADOQuery1.Active := True;
if prReport2.PrepareReport then
prReport2.PreviewPreparedReport(true);

Thank your , again!