[SOLVED]How do you default a paramenter date in a fex?
I have a fex that will retrieve all invoices created in a date range. It contains several amper variables. For simplicity, I will discuss only the date range. The MR HTML file allows the user to choose a begin and end create date range. I want to add another line in the fex that limits what ever they pick to a 6 month max range subtracting 6 months from the end date. In this case, the user entered a 12 year date range. I would like a way to add a where in the fex that says the begin date (&BDATE) is less than the end date (&EDATE) minus 6 months but keep the &BDATE since it could be a range of only 1 month. I see how to default a date based on the current date but our date ranges can vary. Does anyone have a solution for this?
TABLE FILE THAT1 PRINT INVOICE CREATE_DATE WHERE CREATE_DATE GE '&BDATE' AND CREATE_DATE LE '&EDATE'; -*add me WHERE CREATE_DATE LE '2011/10/01'; ON TABLE HOLD AS THAT2 END
For example, the user enters 2010/03/01 as &BDATE and 2012/03/31 as &EDATE. I want to add an additional line to the fex that says &BDATE is LE 2011/10/01 (6 months).
Thank you, GeriThis message has been edited. Last edited by: Geri,
The easiest way to do this would be something like the following:
WHERE CREATE_DATE LE DATEADD(&BDATE,'M',6);
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
March 29, 2012, 11:08 AM
njsden
quote:
add a where in the fex that says the begin date (&BDATE) is less than the end date (&EDATE) minus 6 months
You can try the code below which will give a 6-month period-cap as needed in a way that will be "SQL-friendly", that is, your iWay adapter will be able to pass the filter for the database to handle.
-DEFAULT &BDATE = '2010/03/01';
-DEFAULT &EDATE = '2010/03/31';
-* Determine 1st day of the 6th month before end date
-SET &EDATE_YYM = EDIT(&EDATE, '9999$99');
-SET &MIN_BDATE = EDIT(AYM(&EDATE_YYM, -6, 'I6YYM'), '9999/99') || '/01';
TABLE FILE FOO
PRINT THIS AND THAT
WHERE FOO_DATE FROM '&BDATE' TO '&EDATE'
WHERE FOO_DATE GE '&MIN_BDATE'
END
That way, you will be limiting your date period to span no more than 6 months while still honouring smaller ranges if the user chooses so.
Hope this helps.This message has been edited. Last edited by: njsden,