Re: Querying a Date Range in FORMS

Ed,

We also are in the process of addressing this user-unfriendliness issue. I am advocating a Form-level When-Mouse-DoubleClick trigger which will call a program unit which tests for Enter-Query mode, Text Item and Date or Datetime datatype. If true then display a window which facilitates the entry of 'start' date, 'end' date, or both, and then formats a string with #BETWEEN or #>=, etc. Rather than carry the window in all fmx's, we are looking at implementing the window as a separate form, loading the string into a global for use by the calling program. The calling program then loads the string into the date item using indirection, which works in enter-Query mode regardless of the datatype. eg. Copy(date_string, Name_In('System.Trigger_Item'));.

The string itself is a little tricky, so here's an example: date_string CHAR(250) :=
  '#BETWEEN TO_DATE(''' ||
  TO_CHAR(start_date,
  'DD/MM/YY HH24:MI') ||
  ''',''DD/MM/RR HH24:MI'') AND TO_DATE(''' ||   TO_CHAR(end_date,'DD/MM/YY HH24:MI') ||   ''',''DD/MM/RR HH24:MI'')';
This creates a string that preserves the 'TO_DATE' and resolves the TO_CHAR.
Date formats 'DD/MM/YY HH24:MI' and 'DD/MM/RR HH24:MI' depend on your application.

We are still using 4.5, so there may be a better 5.0 solution.

Regards,
Neville

Ed Jennings <jenningse_at_mindspring.com> wrote in article <35E1D892.A495FFC0_at_mindspring.com>...
> I have a date field in a FORM. Sometimes the user wants
> to query a range of dates, as opposed to a particular date.
> How can this be done. The users have no knowledge of SQL, and
> couldn't construct an 'AND' clause if their life depended on it.
> What they would like to enter is something like:
>
> 15-AUG-98..18-AUG-98
>
> The FORM needs to be smart enough to recognize a single date or
> a range of dates. Anyone know how this can be done without
> introducing a second date field?
>
> TIA
>
> Ed Jennings
>
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> jenningse_at_mindspring.com
>
> "The opinions expressed here are my own, not those of DOMAIN
> technologies"
>
>
>
Received on Tue Aug 25 1998 - 07:19:14 CEST