Formula for Last Day of the Month

[Huddle]

I want this formula to calculate either 3 years or 5 years depending upon the FSL. But I want it to enter the last day of the month. For example if the date of the Last_FSA entered was 4/14/12 and the FSL is 3 I want the result to be 4/30/15. Also if no Last_FSA date is entered I want the last day of the current month. How do I adjust my formula to make this work?



=IIf([FSL]=3,[Last_FSA]+1095,IIf([FSL]=4,[Last_FSA]+1095,IIf([FSL]=2,[Last_FSA]+1825,IIf([FSL]=1,[Last_FSA]+1825,IIf([FSL]=" ",Date())))))

[JoeM]

There are a number of ways to do this. Here is one that I have listed below where you split the process into two steps.
The first calculation returns the date 3 or 5 years from now (or the current date):

TempDate: IIf(IsNull([Last_FSA]),Date(),DateAdd("yyyy",IIf([FSL]>2,3,5),[Last_FSA]))

Then, to get the end of month from this, we can use this calculation:

CalcDate: DateSerial(Year([TempDate]),Month([TempDate])+1,0)

You can obviously combine these into one calculation, just substituting in the TempDate calculation into the two TempDate references in the CalcDate calculation. I split them apart so the logic becomes more apparent (the huge formula can look a bit intimidating)!

Personally, I would probably creating a UDF in VBA to do this. To me, that are easier to maintain, and you can document it with comments. If you would rather see a UDF/VBA solution, let me know, and I can probably whip it up.

[Huddle]

I would like to see that and know how that works.

[JoeM]

All right. Place this VBAS code in a new module in Access:

Function SpecialDateCalc(FSA_Date As Variant, FSL As Integer) As Date

    Dim myTempDate As Date
    
'   If date field is blank, return current date...
    If IsNull(FSA_Date) Then
        myTempDate = Date
    Else
'   ... else calculate date based on FSL value
        Select Case FSL
            Case 1, 2
                 myTempDate = DateAdd("yyyy", 5, FSA_Date)
            Case 3, 4
                 myTempDate = DateAdd("yyyy", 3, FSA_Date)
            Case Else
                 myTempDate = Date
        End Select
    End If
    
'   Go to end of month of calculated date
    SpecialDateCalc = DateSerial(Year(myTempDate), Month(myTempDate) + 1, 0)
    
End Function

Now, to use it in any type of calculation (i.e. in a query or VBA), you simply use it like any other native Access functions, i.e. in your query:

MyDateCalc: SpecialDateCalc([FSA_Date], [FSL])

[Huddle]

It works great. Thanks.