0

I'm storing dates on MS ACCESS database along with other information. But I can't seem to search them as I please. I'd like to search by: year, month. I'm using a datetimepicker

Currently I'm using this code to do so: ADOTable1.Filter := 'Date > ' + Edit1.Text; ADOTable1.Filtered := True; end;

Could anyone help me?

Ammadeux
  • 325
  • 1
  • 5
  • 15
  • Have you seen [the first hit](http://delphi.about.com/od/delphitips2007/qt/datetime_sql.htm) on *"Delphi MS Access date time"* term on Google ? – TLama Feb 12 '13 at 12:01
  • @TLama Dataset filter is independed from database – bummi Feb 12 '13 at 12:53

1 Answers1

2

You have two options: your solution uses the less preferable one of passing string literals to a query. In this case, you have to 'escape' the value that you are passing, eg

ADOTable1.Filter := 'Date > ''' + Edit1.Text + '''';

This will result in a line like

ADOTable1.Filter := 'Date > ''27-Sep-69'''

The better solution is to use a parameterised query 

select <whatever> from table
where date > :p1

You pass the parameter in the following manner

ADOTable.parambyname ('p1').asdate:= strtodate (edit1.text);

I admit that I don't use ADO components so that syntax may be slightly off, but it's the syntax used for Firebird.

Parameters are better than using raw text values because you don't have to worry about adding the correct number of quotation marks, and no one can pass in a bad value. Imagine what would happen if edit1.text contained '27-Sep-65;Drop table1'; - this could delete your table from the database!

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • `ADOTable1.parambyname ('Date').asdate:= strtodate (edit1.text);` IT won't work, I get erro on `parambyname` and `.asdate` – Ammadeux Feb 12 '13 at 16:53
  • @Ammadeux: Is there a 'TAdoQuery' component? Use that instead of TAdoTable. You may have to change 'asdate' to 'asdatetime'. – No'am Newman Feb 13 '13 at 04:15
  • I've added a `Tadoquery` and in the SQL property I've put : `select from table where data > :p1 ` But I get this error: object parameter was not correctly defined, information was given incorrectly. – Ammadeux Feb 14 '13 at 01:18
  • @Ammadeux: By , I mean "list of fields that you want the query to return", eg select name from table where date > :p1. Also you have to define the parameter within the query component and select its type. – No'am Newman Feb 14 '13 at 04:18
  • Define your query in the TADOQuery.SQL property. Then go to the 'parameters' property, press on the three dots, and in the dialog box that appears (editing), press Insert to add a new parameter. Name the parameter p1 and set its datatype to ftDate. – No'am Newman Feb 14 '13 at 10:55
  • I could get the adoquery to activate, but `ADOTable1.parambyname ('p1').asdate:= strtodate (edit1.text);` gets error. – Ammadeux Feb 14 '13 at 21:41
  • @Ammadeux: try "parambyname ('p1').asdatetime" instead – No'am Newman Feb 15 '13 at 15:15