IF THEN ELSE OR AND MORE

I seem to have a problem with finding where my parens go in this formula and feel as if I have tried them all.

Can anyone help shed some light on this for me?

If {AUT.REFTP} = "HPC" and {AUT.ESTADDATE} in {@Beg date} to {@End date} then "H New"
Else
If {AUT.REFTP} = "HPC" and {AUT.ESTADDATE} < {@Beg date} and {AUT.STAT} = "AHPC" then "HPC Existing"
Else
If {AUT.REFTP} = "HPC" and {V_SDENDSRVCDT.ENDSRVCDT} between {@Beg date} to {@End date} and {AUT.ESTADDATE} = "AHPCC" then "HPC Existing"
Else
If {AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} in {@Beg date} to {@End date} then "D New"
Else
If {AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} < {@Beg date} and isnull {V_SDENDSRVCDT.ENDSRVCDT} or {V_SDENDSRVCDT.ENDSRVCDT} between {@Beg date} to {@End date} then "D Existing"

I know I should know this and might just need to walk away for a day and come back, but I don't have that timeframe unfortunately. Any help or light to help shed on this, very much appreciated in advance.   

Hi

Try below :

If {AUT.REFTP} = "HPC" and {AUT.ESTADDATE} in {@Beg date} to {@End date} then "H New"
Else
If {AUT.REFTP} = "HPC" and {AUT.ESTADDATE} < {@Beg date} and {AUT.STAT} = "AHPC" then "HPC Existing"
Else
If {AUT.REFTP} = "HPC" and {V_SDENDSRVCDT.ENDSRVCDT} in {@Beg date} to {@End date} and {AUT.ESTADDATE} = "AHPCC" then "HPC Existing"
Else
If {AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} in {@Beg date} to {@End date} then "D New"
Else
If {AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} < {@Beg date} and (isnull ({V_SDENDSRVCDT.ENDSRVCDT}) or {V_SDENDSRVCDT.ENDSRVCDT} in {@Beg date} to {@End date}) then "D Existing"

Edited by Sastry - 21 Jul 2017 at 1:27am

I am still getting the "The ) is missing" error on the very last line and it highlights the "then"......Thank you for your help!!

Hi,
Is this a full formula or you have any other lines which you have not pasted here?

All the open brackets and tallying with closing. Still I have covered each line within brackets. Try blow:
If ({AUT.REFTP} = "HPC" and {AUT.ESTADDATE} in {@Beg date} to {@End date}) then "H New"
Else
If ({AUT.REFTP} = "HPC" and {AUT.ESTADDATE} < {@Beg date} and {AUT.STAT} = "AHPC") then "HPC Existing"
Else
If ({AUT.REFTP} = "HPC" and {V_SDENDSRVCDT.ENDSRVCDT} in {@Beg date} to {@End date} and {AUT.ESTADDATE} = "AHPCC") then "HPC Existing"
Else
If ({AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} in {@Beg date} to {@End date}) then "D New"
Else
If ({AUT.REFTP} = "DIA" and {AUT.STAT} = "App" and {AUT.ESTADDATE} < {@Beg date} and (isnull ({V_SDENDSRVCDT.ENDSRVCDT}) or {V_SDENDSRVCDT.ENDSRVCDT} in {@Beg date} to {@End date})) then "D Existing"

Hi -

Yes this is the full formula that I pasted and I tried this last suggestion, now I am getting a "String is required here" on my Beg to End dates in line 3. I am so thankful for your help, it's just been a little frustrating trying to figure out what I am doing wrong....   

Hi

If you look at the 3rd line, I see that you are not comparing your @begindate and Enddate with date field

If ({AUT.REFTP} = "HPC" and {AUT.ESTADDATE} in {@Beg date} to {@End date} and {V_SDENDSRVCDT.ENDSRVCDT}
= "AHPCC") then "HPC Existing"

I have corrected it in above post, replace the third line with the updated one and check

That worked!!!!

Thank you again very much for your help!!! Happy Friday