Unanswered: How to correctly get last day of the month?
I have a function that returns an array of dates that are supposed to be the last day of x number of months back. The function seems to work fine for months that end in 31, but fails for months that only have 30 days. When I use dateadd("m",-1,#4/30/2004#) Access 97 returns 3/30/2004. I suppose this is technically accurate, but it isn't doing what I need it to do. Can someone please provide me with some help here and how to make this work.
Function lastDayOfMonthsArray(howManyMos, Optional startingAt)
Dim sqlSelect As String
Dim mos() As Date
Dim nextMonth, endOfMonth As Date
If IsMissing(startingAt) Then
startingAt = Date
If howManyMos < 1 Then
MsgBox "The number of months value for lastDayOfMonthsArray is invalid"
ReDim mos(howManyMos - 1) As Date
Dim firstOfMonth, histDate As Date
Dim x As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
firstOfMonth = CDate(DatePart("m", startingAt) & "/1/" & DatePart("yyyy", startingAt))
nextMonth = DateAdd("m", 1, firstOfMonth)
endOfMonth = DateAdd("d", -1, nextMonth)
For x = 0 To howManyMos - 1
mos(x) = DateAdd("m", -x, endOfMonth)
lastDayOfMonthsArray = mos()
The trick is not to use the last day of a month as your starting point. Always work from the first day of a month (since it is the same every month) and then subtract 1 day to get the last day of the previous month. So, if you want the last day of last month (April), start with this month (May), hard code the day ("1"), and use DateAdd to subtract 1 day.