Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    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
    End If
    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()
    End If
    End Function

  2. #2
    Join Date
    May 2004
    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.

  3. #3
    Join Date
    May 2004

    Thanks. I guess I had tried that, but didn't stay with that methodology all of the way through the loop.

    Thanks, I'll try it again.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts