PRODUCT   :  R:BASE                  VERSION      :  3.1
   From HB Sloan, 1288 W. 11th St. #117, Tracy, CA 95376. You can reach
   HB at 209-836-5713.
   Applications frequently need to construct a WHERE clause based on two
   dates supplied by the person using the application. For example, you
   might want to print a report for rows that fall between two dates, or
   browse through rows entered since 6/1/91 or before 1/1/91.
   To fill this need, I developed a generic utility that I call
   WHEDATE.CMD (listed below and included in 0891MRIM.ZIP) to construct
   the WHERE clause. It uses a pop-up screen (WHEDATE.SNP) to collect the
   beginning and ending dates. Use the program MAKESNAP.CMD (listed
   below) to make WHEDATE.SNP.
   When WHEDATE.CMD asks for the beginning and ending dates, you can
   enter either date or both dates. If you enter only a beginning date,
   the program assumes you want all the dates since that date
   (inclusive). If you enter only an ending date, it assumes you want all
   the dates up to and including that ending date.
   What WHEDATE.CMD Does
   WHEDATE.CMD takes a snapshot of the current screen (TMP.SNP), collects
   the dates using WHEDATE.SNP as a pop-up screen, and later restores the
   screen by displaying TMP.SNP.
   WHEDATE.CMD uses the collected dates to construct the WHERE clause,
   which it puts into the variable PV_BETW before returning control to
   the calling program.
   WHEDATE.CMD has a default value feature. Set up default values for
   either or both of the two dates by setting PV_DATE1 or PV_DATE2 to a
   specific date before running WHEDATE.CMD. WHEDATE.CMD displays */*/*
   for any date you leave null. During execution, WHEDATE.CMD uses the
   CVAL function to store original environment conditions, which it
   restores at the end of the routine.
   How to Make WHEDATE.SNP
   WHEDATE.CMD requires a snapshot file named WHEDATE.SNP. To make it,
   use CONNECT to open any database. Then set the background color to the
   background color of the application, and run this program:
     *( snapshot file required by WHEDATE.CMD.)
     CREATE TABLE snapper (snapper TEXT 63)
     SET VAR vrow1 = (SFIL(' ',22) + 'Enter Date Range' +
       + SFIL(' ',22) + CHAR(255))
     LOAD snapper
     .vrow1; ' '; ' '; ' '; ' '
     WRITE ' Press [Enter] ' AT 20,30 white ON red
     CHOOSE v1 FROM #VALUES FOR snapper FROM snapper AT 10,10
     SNAP whedate.snp FROM 10,10 TO 17,76
     REMOVE TABLE snapper
   MAKESNAP.CMD makes the snapshot file by taking a snapshot of an actual
   pop-up box where all the choices are blank lines. You only need to run
   it once unless you change the application's background color.
   When you run WHEDATE.CMD, you see what appears to be a true pop-up box
   because WHEDATE.SNP is based on a real pop-up and WHEDATE.CMD restores
   the original screen.
   How to Run WHEDATE.CMD
   Use the WHEDATE.CMD file included in this MRIM0891.ZIP file and run
   MAKESNAP.CMD to create WHEDATE.SNP. Then run WHEDATE.CMD by passing
   the following three parameters in the USING clause:
     o  Screen line number (1 to 19) to display WHEDATE.SNP.
     o  Screen column number (1 to 14) to display the top left corner of
     o  Name of the DATE column that you want referenced in the WHERE
   For example, with the CONCOMP database, you might use the following
   command to collect and display transaction records falling in a date
     RUN whedate.cmd USING 10,10,'transdate'
     SELECT * FROM transmaster &pv_betw
   Listing of WHEDATE.CMD
     *( WHEDATE.CMD--Create and return a variable PV_BETW)
     *( that contains a WHERE clause specifying a DATE range.)
     *( It displays a snap file named WHEDATE.SNP to collect)
     *( beginning and ending dates. Dates left null are shown as  */*/*.)
     *( Before running, you can set default values by setting PV_DATE1)
     *( to a beginning date and PV_DATE2 to an ending date. When)
     *( you run WHEDATE.CMD include the following three parameters)
     *( in USING clause:  ...USING scrnline, scrnpos, datecol)
     *( SCRNLINE--screen line number (1 to 19) to display snap file.)
     *( SCRNPOS--position (1 to 14) on the line to display snap file.)
     *( DATECOL--name of the DATE column for the WHERE clause.)
     *( Example:  RUN whedate.cmd USING 10,10 'transdate')
     *( Then use PV_BETW in a command like this:)
     *( Example:  SELECT * FROM transmaster &pv_betw)
     *( WHEDATE.CMD requires the snap file WHEDATE.SNP and)
     *( returns the WHERE clause in the variable PV_BETW. It stores)
     *( the original environment and snaps the original screen. Then it)
     *( restores the screen and environment at the end of the program.)
     SNAP tmp.snp  *( Snap the current screen.)
     SET ERROR VAR sv_errv
     *( Use CVAL to store current environment to restore at the end.)
     SET VAR lv_oldmess = (CVAL('MESSAGE')), +
       lv_olderr = (CVAL('ERROR')), oldmany = (CVAL('MANY'))
     SET VAR vbar TEXT = (CHAR(219)), vbar = (SFIL(.vbar,24)), +
       lv_dateT TEXT = '*/*/*', lv_org_row INTEGER = .%1, +
       lv_org_col INTEGER = .%2, lv_column TEXT = .%3, +
       pv_date1 DATE, pv_date2 DATE, +
       pv_betw TEXT = ( 'WHERE' & .lv_column ), +
       lv_r3 = (.lv_org_row + 3), lv_r4 = (.lv_org_row + 4), +
       lv_c5 = (.lv_org_col + 5), lv_c30 = (.lv_org_col + 30), +
       lv_c40 = (.lv_org_col + 40)
     *( Change the MANY setting so you can clear percent variables.)
     SET MANY=*
     CLEAR VAR %1, %2, %3
     SET MANY=%
     CLS; DISPLAY whedate.snp AT .lv_org_row .lv_org_col
     WRITE ' Enter Beginning Date: ' AT .lv_r3 .lv_c5 white ON gray
     WRITE '          ' AT .lv_r3 .lv_c30  *( 10 spaces inside quotes.)
     WRITE '    Enter Ending Date: ' AT .lv_r4 .lv_c5 white ON gray
     WRITE '          ' AT .lv_r4 .lv_c30  *( 10 spaces inside quotes.)
     LABEL date1
     WRITE .vbar AT .lv_r3 .lv_c40 gray
     FILLIN pv_date1=10 AT .lv_r3 .lv_c30 EDIT
     IF sv_errv <> 0 THEN
       WRITE ' <--Invalid DATE.' AT .lv_r3 .lv_c40 gray ON black BLINK
       PAUSE 2
       GOTO date1
     SHOW VAR pv_date1=10 AT .lv_r3 .lv_c30
     SET VAR lv_last = (LASTKEY(0))
     IF lv_last = '[Esc]' OR lv_last = '[Up]' OR lv_last = '[PgUp]' THEN
       SET VAR pv_betw = ' '
       GOTO goback
     IF pv_date1 IS NULL THEN
       WRITE .lv_datet AT .lv_r3 .lv_c30
     LABEL date2
     WRITE .vbar AT .lv_r4 .lv_c40 gray
     FILLIN pv_date2=10 AT .lv_r4 .lv_c30 edit
     IF sv_errv <> 0 THEN
       WRITE ' <--Invalid DATE.' AT .lv_r4 .lv_c40 gray ON black BLINK
       PAUSE 2; GOTO date2
     SHOW VAR pv_date2=10 AT .lv_r4 .lv_c30
     SET VAR lv_last = (LASTKEY(0))
     IF lv_last = '[Esc]' OR lv_last = '[Up]' OR lv_last = '[PgUp]' THEN
       GOTO date1
     IF pv_date2 IS NULL THEN; WRITE .lv_datet AT .lv_r4 .lv_c30
     *( Check for both dates null.)
     LABEL both_null
     IF pv_date1 IS NULL AND pv_date2 IS NULL THEN
       SET VAR pv_betw = (.pv_betw & 'IS NOT NULL' ), +
         pv_date1 TEXT = .lv_datet, pv_date2 TEXT = .lv_datet
       GOTO goback
     *( Check for only an ending date.)
     IF pv_date1 IS NULL AND pv_date2 IS NOT NULL THEN
       SET VAR pv_betw = (.pv_betw & '<=' & CTXT(.pv_date2)), +
         pv_date1 TEXT = .lv_datet
       GOTO goback
     *( Check for only a beginning date.)
     IF pv_date1 IS NOT NULL AND pv_date2 IS NULL THEN
       SET VAR pv_betw = (.pv_betw & '>=' & CTXT(.pv_date1) ), +
         pv_date2 TEXT = .lv_datet
       GOTO goback
     *( Both dates are present. Check them.)
     LABEL no_null
     IF pv_date1 = pv_date2 THEN
       SET VAR pv_betw = (.pv_betw & '=' & CTXT(.pv_date1))
       GOTO goback
     *( Check for beginning date bigger than ending date.)
     IF pv_date1 > .pv_date2 THEN
       PAUSE 1 USING +
         'Beginning date is after ending date, so all dates included.'
       SET VAR pv_date1 = NULL, pv_date2 = NULL
       GOTO both_null
     SET VAR pv_betw = (.pv_betw & 'BETW' & CTXT(.pv_date1) +
       & 'AND' & CTXT(.pv_date2))
     LABEL goback
     CLS; DISPLAY tmp.snp  *( Redisplay the current screen.)
     DEL tmp.snp
     SET ERROR MESSAGES .lv_olderr; SET MESSAGES .lv_oldmess
     CLEAR VAR lv%, pv_date%, vbar, sv_errv
     SET MANY=.oldmany; CLEAR VAR oldmany
   WHEDATE.CMD's Four Possibilities
   WHEDATE.CMD handles all four possible combinations. For example, if
   the column name passed in the USING clause is TDATE, here are the four
   possible WHERE clauses:
     o  Null beginning & null ending date: WHERE tdate IS NOT NULL.
     o  8/1/91 ending date & null beginning date: WHERE tdate <= 8/1/91.
     o  7/1/91 beginning date & null ending date: WHERE tdate >= 7/1/91.
     o  7/1/91 beginning date & 8/1/91 ending date: WHERE tdate BETWEEN
        7/1/91 AND 8/1/91.