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,
Geri

This 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);

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,

njsden

Thank you, this worked perfect.

You're welcome, Geri. I'm glad it worked!