I am really struggling to write a report and I am looking for some help.

 

First of all I am using this formula which is the major driving formula in my report

 

IIF(DATE(YEAR(TODAY()),MONTH({@Date Pol COm}),DAY({@Date Pol COm})) < TODAY(),DATE(YEAR(TODAY())+1,MONTH({@Date Pol COm}),DAY({@Date Pol COm})),DATE(YEAR(TODAY()),MONTH({@Date Pol COm}),DAY({@Date Pol COm})))

 

What this is doing is taking the clients start date and then seeing if the anniversary has passed this year or is due this year. If the anniversary has passed it displays the date 29/01/2014 as today is 30/01/2013.

 

This works ok which is great.

 

The problem is, I am looking for all plans that have yesterday's date. If I try any selection criteria using this formula field it displays the following message;

 

"A day number must be between 1 and the number of days in the month"

 

I can't even sort the report using this column. I am confused. Please can anybody help. Maybe there is a better way of writing my formula?

 

Regards

Gavin

 

 

 

date({POLICYINFORMATIONVW.POLICYCOMMDATE})

 

I was just trying to convert the field first before using it in the formula to see if that worked.

 

The formula doesn't work with just ({POLICYINFORMATIONVW.POLICYCOMMDATE}) either. This field is DATETIME, but the time is always 00:00:00

 

Don't know if that helps?

 

Thanks for looking