How do you apply a Delphi ADOTable filter to a date data type

Question

in MS Access I am able to filter a date in a query like this: ex.

SignUpDate > #31/12/2013#

this will make the database only show records where SignUpDate is in 2014 or newer

How will I do this in delphi?

dmGym.tblMembers.filter := 'SignUpDate > ''#31/12/2013#''' doesn't seem to work

please help it wil be greatly appreciated

Answer 1

Try dmGym.tblMembers.filter := 'SignUpDate > #yyyy/mm/dd#' (2013/12/31)

-credit to kobik's comment

Answer 2

I think you don't need the #s, try

[...].Filter := '12/31/2013';  // this is for Sql Server and tested 
  //  for UK locale, for Access you may need to swap the dd and mm, 
  //and maybe even the yyyy as suggested by @kobik in a comment.

If you use a TDateTime field in a TAdoDataSet.Locate(), the function GetFilterStr plugs the # signs in for you (and makes a hash of it (spot the pun) when doing similar for string fields - see # signs in ADO locates (Delphi XE5)).

But setting a simple filter on a TAdoTable seems to bypass ADODB.GetFilterStr and does a direct assignment to its recordset's Filter property, so I'm guessing that if the #s are needed, they must be plugged in by the ADO/MDac layer.

Answer 3

Delphi stores the datetime as a real number. Today is 05 June 2016 and the interger part of the DateTime is 42,526. Date zero is the start of 1900. You need to generate a variable called DateInt.

Var DateInt: integer Date1: TDate; Begin DateInt := Trunc(Date1)

When you save a date, save DateInt in the BeforePost event handler. This is an extra field, but filtering is now easy. For example, your filter can now be

NewDateInt := Trunc(Date1); Filter := ‘NewDate = DateInt’;

Answer 4

You can try :

dmGym.tblMembers.Filter:='SignUpDate > 31/12/2013';
dmGym.tblMembers.Filtered:=True;

This will make the database only show records where SignUpDate is in 2014 or newer.

Good Luck.