IIF Statement using Date() Function

[Martano0811]

Here is the If statement flag :

CPC and MM Flag: IIf(Date()-[CPC Milestone 14+].[CPC and MM approves floorplan Target]-[CPC Milestone 14+].[CPC and MM approves floorplan Actual]>"14","14+")

The CPC and MM approves Target/Actual fields are in Date/Time format


I would like to calculate a field based on todays date but its not calucalating properly Is there a ways to get this date query to work? i would lke to get a 14+ on all rows that are 14 days past the target date.

 

[pbaldy]

You wouldn't want the quotes around the value in the test portion. I'd include a False argument, even if it's just "".

 

[Pat Hartman]

You have two subtraction operations. How are you expecting that to work? The first one returns a difference in days and so the second operation is subtracting a date from some number of days. This won't work. What are you really trying to do?

 

[Brianwarnock]

If you are looking for projects that are 14 days overdue then it is merely Date()-Target, but first you need to find if the project is overdue, what is in Actual in this case?

Probably you need
Iif(actual is null,"",Iif(Date()-Target >14,"14+",""))

Brian