Bring back last 3 months of data

[shaunacol]

I want to show the last 3 months of data based on a date field. MajP gave me this code which I have in a module and I callupon. However, I have just noticed that it brings back the last 3 months of data regardless of the year. Can I chnage it to ensure it brings back the last 3 months only? I expect this is probably quite simple but I am very rusty...thanks in advance for any help.



Public Function LastDateM(dtmDate)
'Return the Last date of provided month
'
LastDateM = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function
Public Function firstDayM(dtmDate)
'Return the First day of provided month
'
firstDayM = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function
Public Function EndDateRange()
LastDateM (Date)
End Function
Public Function startDateRange(MonthsPrevious As Integer)
'
startDateRange = firstDayM(LastDateM(Date) - (MonthsPrevious) * 31)
End Function

 

[MajP]

I do not know if I posted this wrong or it got copied wrong:

Public Function EndDateRange()
LastDateM (Date)
End Function

That does not return anything. Should be

Public Function EndDateRange()
EnddateRange = LastDateM (Date)
End Function


So using the functions and you want to look 2 months back (This month and 2 previous months)
Your start date should be 12/1/2008 and your end range should be 2/28/2009

Test:
?startDateRange(2)
12/1/2008
?endDateRange()
2/28/2009

If this is used in a query correctly as the criteria then it will return only records within these ranges. It will return a date in Dec, Jan, Feb from previous years not between 12/1/2008 - 2/28/2008

 

[MajP]

This may also give you more flexibility.

Public Function EndDateRange(Optional theDate As Date = 0)
   If theDate = 0 Then theDate = Date
   EndDateRange = LastDateM(theDate)
End Function
Public Function startDateRange(Optional MonthsPrevious As Integer = 2, Optional theDate = 0)
   If theDate = 0 Then theDate = Date
   startDateRange = firstDayM(LastDateM(theDate) - (MonthsPrevious) * 31)
End Function

If you provide no parameters it defaults to a start date two months prior
startDateRange()
12/1/2008

Or you can provide a "monthsPrior" argument. In this case 0 months prior so the start is this month
?startDateRange(0)
2/1/2009

Or you can provide a MonthsPrior and a date. I this case I want a start date 2 months prior to april 1970.

startDateRange(2,#4/19/1970#)
2/1/1965

With the end date no argument returns the end of the current month.
?EndDateRange()
2/28/2009

if you provide a date it returns the end of that month.
?EndDateRange(#4/19/1970#)
4/30/1970


This may be helpful if you need to run a report for a prior three month period not just three months from this month.

 

[shaunacol]

That works fantastically - thanks for your quick response!!!