MS Access formula to derive minimum date-CodePudding

The below formula is giving error. Please help me solve my problem.

IIf((GetReportingDate()-[Final_Data]![Start Date])>(365*5*4),
  MIN([Final_Data]![Maturity Date] ,(DateAdd(“YYYY”,25, [Final_Data]![Start Date])),
IIf((GetReportingDate()-[Final_Data]![Start Date])>(365*5*3), 
  MIN([Final_Data]![Maturity Date] ,(DateAdd(“YYYY”,20, [Final_Data]![Start Date])),
IIf((GetReportingDate()-[Final_Data]![Start Date])>(365*5*2), 
  MIN([Final_Data]![Maturity Date] ,(Dateadd(“YYYY”,15, [Final_Data]![Start Date])),
IIf((GetReportingDate()-[Final_Data]![Start Date])>(365*5), 
  MIN([Final_Data]![Maturity Date],(Dateadd(“YYYY”,10, [Final_Data]![Start Date])),
IIf(([Final_Data]![Start Date] 365*5)<[Final_Data]![Maturity Date], 
  MIN([Final_Data]![Maturity Date],
    (Dateadd(“YYYY”,5, [Final_Data]![Start Date])),[Final_Data]![Maturity Date]))))),
  "Check")))

Getting Syntax error. Not getting minimum date

Apologies for long post. I am trying to derive re-pricing dates for loans stored in a table. The loan resets every 5 years and I do not have the loan previous re-set date info. I am trying to do it through Access Update Query Function. The above formula should address the below requirement.

If loan start date is more than 20 yrs from reporting date then Min(Start date 25 years, Loan Maturity Date)
If loan start date is more than 15 yrs from reporting date then Min(Start date 20 years, Loan Maturity Date)
If loan start date is more than 10 yrs from reporting date then Min(Start date 15 years, Loan Maturity Date)
If loan start date is more than 5 yrs from reporting date then Min(Start date 10 years, Loan Maturity Date)
If loan start date is less than 5 yrs from reporting date then Min(Start date 5 years, Loan Maturity Date)
If above conditions are not met, then Re-pricing date=Maturity Date

CodePudding user response：

You have an undefined MIN function
Some closing parentheses were missing
Smart quotes won't work. Use normal double-quotes: "
Some years are leap years having 366 days
Even with correct syntax, the expression appears as overly complicated

IIf(
    (GetReportingDate()-[Final_Data]![Start Date])>(365*5*4),
    MIN([Final_Data]![Maturity Date], DateAdd(“YYYY”,25, [Final_Data]![Start Date]),
    
IIf(
    (GetReportingDate()-[Final_Data]![Start Date])>(365*5*3), 
    MIN([Final_Data]![Maturity Date], DateAdd(“YYYY”,20, [Final_Data]![Start Date]),

IIf(
    (GetReportingDate()-[Final_Data]![Start Date])>(365*5*2), 
    MIN([Final_Data]![Maturity Date], Dateadd(“YYYY”,15, [Final_Data]![Start Date]),

IIf(
    (GetReportingDate()-[Final_Data]![Start Date])>(365*5),
    MIN([Final_Data]![Maturity Date], Dateadd(“YYYY”,10, [Final_Data]![Start Date]),

IIf(
    ([Final_Data]![Start Date] 365*5)<[Final_Data]![Maturity Date],
    MIN([Final_Data]![Maturity Date], Dateadd(“YYYY”,5, [Final_Data]![Start Date]),

[Final_Data]![Maturity Date]))))),"Check")))))

So, explain in plain English what you are trying to calculate, at best with some sample data and expected results.

A candidate for the MIN function could be this of mine:

' Returns the minimum date/time value of elements in a parameter array.
' If no elements of array Dates() are dates, the maximum value of Date is returned.
'
' Example:
'   DateMax(Null, "k", 0, -5, Date) -> 1899-12-25.
'
' 2016-02-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateMin( _
    ParamArray Dates() As Variant) _
    As Date

    Dim Element     As Variant
    Dim MinFound    As Date
      
    MinFound = MaxDateValue
    
    For Each Element In Dates()
        If IsDateExt(Element) Then
            If VarType(Element) <> vbDate Then
                Element = CDate(Element)
            End If
            If Element < MinFound Then
                MinFound = Element
            End If
        End If
    Next
    
    DateMin = MinFound
  
End Function

It uses a constant and a helper function. All of these can be found at my project at GitHub:

VBA.Date

Addendum:

I believe, an exact calculation taking leap years into account can be done on the fly (when needed) in VBA without updating anything using two of my functions, DateMin and Age, from my library linked to above:

MinimumDate = DateMin(DateAdd("yyyy", 5 * (1   Age(StartDate, ReportingDate) \ 5), StartDate), MaturityDate)

Examples:

StartDate = #2015-12-12#
MaturityDate = #2025-12-31#
ReportingDate = #2022-12-14#
? DateMin(DateAdd("yyyy", 5 * (1   Age(StartDate, Date) \ 5), StartDate), MaturityDate)
2025-12-12

StartDate = #2015-12-12#
MaturityDate = #2024-12-31#
ReportingDate = #2022-12-14#
? DateMin(DateAdd("yyyy", 5 * (1   Age(StartDate, Date) \ 5), StartDate), MaturityDate)
2024-12-31