This discussion is archived
4 Replies Latest reply: Oct 22, 2012 10:22 PM by 690743 RSS

Date parameter passed through Form, wrongly taken by Reports

690743 Newbie
Currently Being Moderated
hello seniors....

i am passing 2 date parameter from my Form6i into Report6i .
format mask of FRM_DATE and UPTO_DATE fields on the form is 'DD/MM/RRRR'
ADD_PARAMETER(PARAM_ID,'P_FRM_DATE', TEXT_PARAMETER,:FRM_DATE);
ADD_PARAMETER(PARAM_ID,'P_UPTO_DATE',TEXT_PARAMETER,:UPTO_DATE);

for example, if i enter FRM_DATE=01/01/2000 and UPTO_DATE=01/01/2050,
i debug by putting in the message both the dates, they gets displayed correctly...before issuing RUN-REPORT command...
Message('Lease_yr :'||TO_CHAR(:frm_date,'DD/MM/YYYY')||' '||TO_CHAR(:upto_date,'DD/MM/YYYY'));
Message(' ');

but in the report's BEFORE_REPORT trigger, when i debug both the dates, i find that report field P_UPTO_DATE is displayed as 01/01/1950
SRW.MESSAGE('1001','B4 :'||TO_CHAR(:P_FRM_DATE,'DD/MM/RRRR')||' '||TO_CHAR(:P_UPTO_DATE,'DD/MM/RRRR'));
and thats causing my report to come as blank....as the date range becomes ambigious...
reports fields P_FRM_DATE and P_UPTO_DATE format mask is DD/MM/RRRR

the condition within report SQL is.....
AND LEASE_UPTO_PERIOD BETWEEN TO_CHAR(:P_FRM_DATE,'DD/MM/YYYY') AND TO_CHAR(:P_UPTO_DATE,'DD/MM/YYYY')

problem with only P_UPTO_DATE....

Please if someone can help with this.....

(Forms [32 Bit] Version 6.0.8.25.2 (Production))
(Report Builder 6.0.8.25.0)
DB 10g XE and Windows7

TY
  • 1. Re: Date parameter passed through Form, wrongly taken by Reports
    tony.g Journeyer
    Currently Being Moderated
    Hi

    I would change in the form:

    ADD_PARAMETER(PARAM_ID,'P_FRM_DATE', TEXT_PARAMETER, to_char(:FRM_DATE, 'DD/MM/YYYY HH24:MI'));
    ADD_PARAMETER(PARAM_ID,'P_UPTO_DATE',TEXT_PARAMETER, to_char(:UPTO_DATE, 'DD/MM/YYYY HH24:MI'));

    Then have the dates as text parameters in the report, then where you use them, explicitly convert them back to date format with to_date().

    rgds
    Tony
  • 2. Re: Date parameter passed through Form, wrongly taken by Reports
    O.Developer Journeyer
    Currently Being Moderated
    HI

    After reading your question,,interestingly tried myself and find it is working without any issue.

    The key point is .....define date parameter type with formatmast as -DD-MON-RRRR' -- both in forms and report.

    And in Add Paramter lsit use To_Char Function instead of passing directly. This will work as you want.

    ---here is my sample code .... calling from when button pressed





    Declare
         
         Pl_Id Paramlist;
         Pl_Name Varchar2(10) :='Testing';
         
         
    Begin
         
    Pl_Id := Get_parameter_list (pl_name);

    IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
    END IF;


    If Id_Null(PL_Id) Then
         Pl_Id := Create_Parameter_list(pl_name);
         
    End If;

    Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
    Add_Parameter(pl_id , 'R_frm_dt', TEXT_PARAMETER , TO_CHAR(:ctrl.frm_dt,'DD-MON-RRRR'));
    Add_Parameter(pl_id , 'R_to_dt', TEXT_PARAMETER , to_char(:ctrl.to_dt,'DD-MON-RRRR'));
         

         
         Run_Product (Reports, 'D:\Run_Product_date_Range',SYNCHRONOUS, RUNTIME,FILESYSTEM, PL_ID, NULL);
         

    End ;
    -------------- Hope it work
  • 3. Re: Date parameter passed through Form, wrongly taken by Reports
    DanielB Pro
    Currently Being Moderated
    what mask have the parameters or are char?

    Edited by: DanielB on Oct 22, 2012 2:45 PM
  • 4. Re: Date parameter passed through Form, wrongly taken by Reports
    690743 Newbie
    Currently Being Moderated
    O.Developer wrote:
    The key point is .....define date parameter type with formatmast as -DD-MON-RRRR' -- both in forms and report.
    And in Add Paramter lsit use To_Char Function instead of passing directly. This will work as you want.
    Add_Parameter(pl_id , 'R_frm_dt', TEXT_PARAMETER , TO_CHAR(:ctrl.frm_dt,'DD-MON-RRRR'));
    Add_Parameter(pl_id , 'R_to_dt', TEXT_PARAMETER , to_char(:ctrl.to_dt,'DD-MON-RRRR'));
    -------------- Hope it work
    Thanks Bro...it worked this time....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points