Filter grid based from 2 dates

Question

I have a grid populated from a dbf. How can I filter data in grid based from two given dates. I have two Calendar Control, one for the start date and other for end date.

SET FILTER TO dateRegistration > THISFORM.olecontrol1.oBJECT.VALUE and dateRegistration <= THISFORM.olecontrol1.oBJECT.VALUE

Answer 1

Boniette, FWIW do not use "SET FILTER" command in VFP, and if you would do anyway, then do not use it with a grid. Its behavior is not predictable, plus when used with a grid, you might (will) get nasty visual effects as well.

Instead, provided your column to "filter on" is indexed, you can use "Set Range". But better yet, since it is unlikely that you would use this grid for editing, use an SQL RecordSourceType and an SQL to "filter" your rows.

Second, be careful with date/time range selections. Your olecontrol's value is a datetime (contains time) and with a datetime value, there is no way to exactly specify the ending value. It is better to have datetime ranges as >= and <. ie: To say all records in Jan 2016, you should have something like this:

... where theDateTime >= datetime(2016,1,1)  and ;
          theDateTime < datetime(2016,2,1)

Note that this range starts at Jan 1st 2016 midnight (inclusive) and ends at Feb 1st 2016 midnight (exclusive) correctly including all records from Jan 2016.

Third, looking at your code, it looks like instead of a range you are trying to filter your records where dateRegistraion's date part is the date chosen, regardless of the time values in it, right? Applying the rule above, then your expression would be:

dateRegistration >= cast(thisform.olecontrol1.object.value as date) and ;
dateRegistration < cast(thisform.olecontrol1.object.value as date)+1

Having said these, your grid would be setup like this:

With Thisform.MyGrid
    .RecordSourceType = 4 && SQL
    TEXT to .RecordSource noshow pretext 15
select * from myTable
where dateRegistration >= cast(thisform.olecontrol1.object.value as date) and
      dateRegistration < cast(thisform.olecontrol1.object.value as date)+1
order by dateRegistration      
into cursor crsData
nofilter
    ENDTEXT
Endwith

When you change the value in your datetimepicker control, you would simply do this to refresh grid:

with thisform.MyGrid
   .RecordSource = .RecordSource
endwith 

Here is a complete working example:

Public oForm
oForm = Createobject('SampleForm')
oForm.Show()

Define Class SampleForm As Form
    Height=400
    Width=600
    Add Object oleControl1 As OleControl With ;
        Top=10, Left=10, Width = 100, Height = 30, ;
        OleClass='MSComCtl2.DtPicker.2'
    Add Object myGrid As Grid With ;
        top=50, Width=600, Height=350

    Procedure Load
        * Create sample data
        Create Cursor myTable (Id i Autoinc, dateRegistration T)
        Rand(-1)
        Local ix
        For ix = 1 To 10000
            Insert Into myTable (dateRegistration) Values ( Datetime() - (Rand()*86400*30) )
        Endfor
        Locate
    Endproc

    Procedure Init
        * Setup Grid
        With Thisform.myGrid
            .RecordSourceType = 4 && SQL
            TEXT to .RecordSource noshow pretext 15
select * from myTable
where dateRegistration >= cast(thisform.olecontrol1.object.value as date) and
      dateRegistration < cast(thisform.olecontrol1.object.value as date)+1
order by dateRegistration
into cursor crsData
nofilter
            ENDTEXT
        Endwith
    Endproc

    Procedure oleControl1.Change
        * Update grid on date change
        *** ActiveX Control Event ***
        With Thisform.myGrid
            .RecordSource = .RecordSource
        Endwith
    Endproc
Enddefine

And one final note:

In FoxyClasses, which I am the author and now has been released to public for a long time, there are over 15 classes that you can use out of the box and make things like this easier. ie: It has PeriodPicker control to get a 'date/time range' from the user, it has locatorGrid control to create a searchable, sortable ... etc grid from an SQL and many more. If you want to check and use its classes, then you can download it from:

FoxyClasses Download