Subject Re: Filter for Date Range
From Patrick Healey <healeyp1947@gmail.com>
Date Wed, 21 Mar 2018 10:06:11 -0400
Newsgroups dbase.getting-started

Dear Mervyn,
You are absolutely correct that dates are tricky.
However, your solution worked.  Using form.date = Dtoc((Date()))
and then a filter with the sequence single apostrophe, double apostrophe, and a plus sign on the front end and reverse on the rear end does work.
Thank you very much.

Pat Healey

Mervyn Bick Wrote:

> On 2018-03-20 8:35 PM, Patrick Healey wrote:
> > Good afternoon!
> > Most of my tables have dates.  I would like to set a filter on a date range.  How is that done?  I have tried ctod(), dtoc(), dtos() and {}.  None worked - or at least not in the combination that I tried.
> > I want to supply a start and end date.  Then filter that on the date field in the table with an expression like:
> > form.xxxtable1.rowset.filter := "(Formdate > Start) AND (Formdate < End)"
> > I set Start = Date() - 365 and End = Date() + 1 in the On Open for the form.
> > Thank you in advance.
> >
> > Pat Healey
> >
>
> Dates can be tricky. :-)  When adding dates to a table using the
> localSQL CREATE TABLE command literal dates must be in the format
> 'mm/dd/yyyy' (American) or 'dd.mm.yyyy' (German) irrespective of the
> date setting for the computer.
>
>
> A filter string is a SQL string but the rules (which aren't spelled out
> as far as I know) are different.  Here the date must be a literal in the
> format of the computer's date setting.  Fortunately dtoc() does that for
> you automatically.  The output from dtoc() needs to be wrapped in single
> quotes in the filter string.
>
> If you are defining your start and end dates in the form's onOpen event
> handler you should save them to user-defined properties of the form.
> That way you can use the values anywhere in the form.
>
> A little example is attached.
>
> Mervyn.
>
>
>
>
> if file('test_date_filter.dbf')
> //  drop table test_date_filter
> endif  
>
> if not file('test_date_filter.dbf')
>    create table test_date_filter  (id autoinc,data character(15),ddate date)
>
>    insert into test_date_filter  (data,ddate) values ("abel",'01/01/2016')
>    insert into test_date_filter  (data,ddate) values ("charlie",'01/01/2018')
>    insert into test_date_filter  (data,ddate) values ("baker",'01/01/2018')
>    insert into test_date_filter  (data,ddate) values ("baker",'05/10/2017')
>    insert into test_date_filter  (data,ddate) values ("charlie",'05/10/2017')
>    insert into test_date_filter  (data,ddate) values ("able",'05/10/2018')
>    // Note that here the date must be a literal in either American format 'mm/dd/yyyy'
>    // or German format 'dd.mm.yyyy' irrespective of the date setting on the computer  
>    
>    use test_date_filter exclusive
>    index on ddate tag ddate
>    use
> endif
> ** END HEADER -- do not remove this line
> //
> // Generated on 2018/03/20
> //
> parameter bModal
> local f
> f = new test_date_filterForm()
> if (bModal)
>    f.mdi = false // ensure not MDI
>    f.readModal()
> else
>    f.open()
> endif
>
> class test_date_filterForm of FORM
>    with (this)
>       onOpen = class::FORM_ONOPEN
>       height = 16.0
>       left = 59.7143
>       top = 5.5
>       width = 75.4286
>       text = ""
>    endwith
>
>    this.TEST_DATE_FILTER1 = new QUERY(this)
>    with (this.TEST_DATE_FILTER1)
>       left = 2.0
>       width = 13.0
>       height = 1.0
>       sql = 'select * from "test_date_filter.DBF"'
>       active = true
>    endwith
>
>    with (this.TEST_DATE_FILTER1.rowset)
>       indexName = "DDATE"
>    endwith
>
>    this.GRID1 = new GRID(this)
>    with (this.GRID1)
>       dataLink = form.test_date_filter1.rowset
>       height = 8.5
>       left = 8.0
>       top = 3.5
>       width = 60.0
>    endwith
>
>    this.PUSHBUTTON1 = new PUSHBUTTON(this)
>    with (this.PUSHBUTTON1)
>       onClick = class::PUSHBUTTON1_ONCLICK
>       height = 1.0909
>       left = 20.0
>       top = 13.5
>       width = 15.2857
>       text = "Set filter"
>    endwith
>
>    this.PUSHBUTTON2 = new PUSHBUTTON(this)
>    with (this.PUSHBUTTON2)
>       onClick = class::PUSHBUTTON2_ONCLICK
>       height = 1.0909
>       left = 39.0
>       top = 13.5
>       width = 15.2857
>       text = "Clear filter"
>    endwith
>
>    this.rowset = this.test_date_filter1.rowset
>
>    function PUSHBUTTON1_onClick()
>       //for a filter the literal date must match the date setting on the compter
>       //dtoc() will give the correct format. Note that the date must be wrapped in single quotes
>       form.test_date_filter1.rowset.filter = "ddate >= '"+form.start+"' and ddate <= '"+form.end+"'"
>       return
>
>    function PUSHBUTTON2_onClick()
>       form.test_date_filter1.rowset.filter = ''
>       return
>
>
>    function form_onOpen()
>       form.start = dtoc((date()-365))
>       form.end = dtoc((date()+1))
>       return
>
> endclass
>