Need to know the last day of a month

[piero1975]

I cannot find a function (such as the SQL LAST_DAY) to assign to variable the last day of a month. The month is a parameter that changes every time....

Can you help me?

I hope I've explained fine this question.

Thanks!

 

[Juan Pablo González]

Using a UDF like this you can get it

Function LastDay(ByVal Dt As Date) As Long
    LastDay = DateSerial(Year(Dt), Month(Dt) + 1, 0)
End Function

 

[Anne Troy]

Or you can use this one to get the first day of the month:

Function FirstOfNextMonth() As Date
   Dim dtm As Date
   dtm = Date
   FirstOfNextMonth = DateSerial(Year(dtm),
   Month(dtm) + 1, 1)
End Function

and then this one to get the last:

Function LastOfThisMonth() as Date
   LastOfThisMonth = DateAdd("d", -1, FirstOfNextMonth)
End Function