7 Replies Latest reply: Dec 9, 2012 11:37 PM by O.Developer

# Months Between format

Guyz,

im doing some calculation in my form below are the formula.
``````MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 +
MONTHS_BETWEEN(SYSDATE,:CTRL.EFF_DATE,/12+
NVL(:CTRL.TOT_TK,0)/12*12``````
the above calculation is working fine but when CTRL.EFF_DATE is null then formula is not working, how can i by pass if EFF_DATE IS NULL then calculate only JOIN_DATE + TOT_TK or else calculate all. JOIN_DATE+EFF_DATE+TOT_TK

Im using forms 6i. oracle 9i.

Regards

Edited by: Moazam Shareef on Dec 9, 2012 12:57 AM
• ###### 1. Re: Months Between format
MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 +
MONTHS_BETWEEN(SYSDATE,:CTRL.EFF_DATE,/12+
NVL(:CTRL.TOT_TK,0)/12*12

----------
If :Ctrl.eff_date not null then

MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 +
MONTHS_BETWEEN(SYSDATE,:CTRL.EFF_DATE,/12+
NVL(:CTRL.TOT_TK,0)/12*12

Else
MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 +
---MONTHS_BETWEEN(SYSDATE,:CTRL.EFF_DATE,/12+
NVL(:CTRL.TOT_TK,0)/12*12

End if;

Edited by: O.Developer on Dec 9, 2012 12:11 PM
• ###### 2. Re: Months Between format
All the above is in formula field so how this if...elsif works?
• ###### 3. Re: Months Between format
You can use CASE WHEN instead of if, this should also work in formula-columns.
• ###### 4. Re: Months Between format
thx Andreas i will try your sugession also....anyhow i solved in different way :)

Regards

Moazam
• ###### 5. Re: Months Between format
How did you solved, on Different way ?...Can you please share with us, will be usefull for rest of us ..Thanks
• ###### 6. Re: Months Between format
Sure why not, i didnt do much, i have the typical requirement to check the employee calculation.

the main thing is that i have to execute this form only at once to check the calculations and there is no addition or records in this block. instead of giving formula in BLOCK.ITEM property i wrote the below code and excute the code after execution of all records in WNFI trigger.
``````IF :EMP.STAT=1 THEN
:BLOCK.ITEM_NAME:= MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 + MONTHS_BETWEEN(SYSDATE,:CTRL.EFF_DATE)/12+
NVL(:CTRL.TOT_TK,0)/12*12
ELSE
MONTHS_BETWEEN(SYSDATE,:CTRL.JOIN_DATE)/12 +
NVL(:CTRL.TOT_TK,0)/12*12
END IF;``````
and ofcourse if user have to add/delete the records the above method will not work . and user have to call this form again ater ammending changes.

Regards

Moazam
• ###### 7. Re: Months Between format
Thankyou