2022-12-17

Access update query formula- Minimum date from multiple criteria

I have created an Access update query and input the below formula in the field "Update to";

IIf(IsNull([Rule2 mapping]![MBO Hier]),"Check",IIf(IsNull([Final_Data]![Start Date]),"Check",IIf([Final_Data]![Start Date]>FixDate2(),IIf((GetReportingDate()-[Final_Data]![Start Date])>(36554),([Final_Data]![Start Date]+36555+4),IIf((GetReportingDate()-[Final_Data]![Start Date])>(36553),([Final_Data]![Start Date]+36554+4),IIf((GetReportingDate()-[Final_Data]![Start Date])>(36552),([Final_Data]![Start Date]+36553+4),IIf((GetReportingDate()-[Final_Data]![Start Date])>(3655),([Final_Data]![Start Date]+36552+4),IIf(([Final_Data]![Start Date]+3655)<[Final_Data]![Maturity Date],([Final_Data]![Start Date]+365*5+4),[Final_Data]![Maturity Date]))))),"Check")))

I would like to add a condition of minimum date as Maturity Date wherever the criteria condition is "True". Access is giving me erratic results. Please help me in adding minimum of Maturity date or [Final_Data]![Start Date]+3655xx

I tried to derive the minimum date using DMIN function but Access threw erratic results



No comments:

Post a Comment