Subject Re: Filter for Date Range
From Mervyn Bick <invalid@invalid.invalid>
Date Tue, 20 Mar 2018 22:43:35 +0200
Newsgroups dbase.getting-started
Attachment(s) test_date_filter.wfm

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