Entering a range of dates as selection criteria

Tony Marston - 1st July 2001

This tip has been tested with Uniface 7.2.06 and Solid 3.0

If you have a database table with a date field it is pretty easy to build a selection screen so that the user can specify a particular date before records are retrieved. But what happens if the user wishes to select on a range of dates, such as between date1 and date2?

The first step is to change the selection screen so that there are two fields for each date, not one. In this example the database field is called START_DATE, and the selection screen contains START_DATE_FROM and START_DATE_TO.

The next step is to define the validation rules. It should be possible to enter no dates at all, any one of the two dates, or both dates. If both dates are entered then the TO date must not be less than the FROM date.

The code in the <validatefield> trigger for START_DATE_FROM would look something like this:

if (@$fieldname != "")           ; field is not empty
   if (START_DATE_TO != "")      ; other field is not empty
      if (@$fieldname > START_DATE_TO)
         message "FROM date cannot be greater than TO date"
         return(-1)
      endif
   endif
endif

The code in the <validatefield> trigger for START_DATE_TO would look something like this:

if (@$fieldname != "")           ; field is not empty
   if (START_DATE_FROM != "")    ; other field is not empty
      if (@$fieldname < START_DATE_FROM)
         message "TO date cannot be less than FROM date"
         return(-1)
      endif
   endif
endif

Before the actual retrieve command can be performed it will be necessary to transfer the contents of START_DATE_FROM and START_DATE_TO (which are non-database fields) into START_DATE with the relevant profile characters. The code should look something like this:

START_DATE = ""
   
if (START_DATE_from != "")
   ; set to 'greater than or equal to'
   $1 = ">=%%START_DATE_from"
endif

if (START_DATE_to != "")
   ; set to 'less than or equal to'
   $2 = "<=%%START_DATE_to"
endif

if (START_DATE_from != "" & START_DATE_to != "")
   START_DATE = "%%$1%%%&%%$2%%%" ; from AND to dates
elseif (START_DATE_from != "")
   START_DATE = $1                ; from date only
elseif (START_DATE_to != "")
   START_DATE = $2                ; to date only
endif

Note:

You will see from this approach that the user does not have to bother with any GOLD profile characters - he enters nothing but dates, and the complicated stuff is handled in the code.

Tony Marston
1st July 2001

mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter