Re: Form Properties. Range High Value

From: CHANCL <CHANCL_at_AM.NIE.AC.SG>
Date: Fri, 29 Dec 1995 12:36:00 +0800
Message-Id: <9512291504.AA32650@alice.jcc.com>

Hi Paul & John !

Thanks 4 your replies.

The problem is solved using the below validation routine in POST-CHANGE trigger.

declare
  diff number;
  testdate number(8) := to_number(to_char(testdate,'YYYYMMDD'));   dbdate number(8) :=

     to_number(to_char(database_date,'YYYYMMDD')); begin
  diff := testdate - dbdate;
  if diff >= 0 then

     display error msg and raise form trigger .....   end if;
end;

PS : Paul, in reply to your earlier mail, I need to use the database date 'cause sometimes if the CMOS battery fails or what not, the operating system date which is sysdate will not be accurate. What I did is to declare another field of date type date and default value = $$DBDATE$$. Paul Dorsey wrote in his 1st reply:
>
> You have to trick it to do date comparisons. Try this in your trigger:
> declare
> dif number;
> begin
> dif := :testdate - sysdate;
> if dif < 0 then
> bell;
> message ('big');
> else
> message ('small');
> end if;
>end;

It does not work.
Let me reiterate my aim : testdate must be earlier than current date. NOTE THAT the field of data type date has the date portion as well as the time portion.

Using Paul's method

declare

        dif number;
begin
  dif := :testdate - sysdate;
  message('The difference='||to_char(dif));   if dif >= 0 then

        message ('Error Message ');
        raise form_trigger_failure;

  end if;
end;

Assuming Sysdate = 29-Dec-1995

Test Case 1
Test Date = 28-Dec-1995 works

Test Case 2
Test Date = 29-Dec-1995 fails
'cause dif = -0.4062.... and not = 0 due to the time portion.

Test Case 3
Test Date = 30-Dec-1995 works

Paul Dorsey wrote in his 2nd reply:
> Wait a minute. I just tried using when_validate_item with your
> origional code and it works fine. I hate to suggest this, but did
> you declare your screen item to be of type "DATE"?

Yes, I did declare it to be of type "DATE' & it still does not work as explain above.

John Thomas wrote :
> Have you named the trigger ON_VALIDATE_FIELD? Forms would
> consider this a user-named trigger & would not execute it.
>
> Just some guesses...

Thanks John. It is not a user-defined trigger.

ChorLing
chancl_at_nievax.nie.ac.sg Received on Fri Dec 29 1995 - 10:04:40 CST