Automate Dates 1-3/ 3-6 / 6-9 / 9-12

[Dave Smith]

I’m looking for a way that I can automate my dates. Instead of going in a couple of times a months going into my query and changing the dates to correct my query.

My typically query outcomes are.

1. Past 3 months starting at the end of the prior month and go back 3 months.
   Past 6 months starting at the end of the prior month and go back 6 months.
   Past 9 months starting at the end of the prior month and go back 9 months.
   Past 12 months starting at the end of the prior month and go back 12 months.
2. List Item

I tried using the formula below, but seem to be pulling by quarters, and if I need to pull a report that shows the past 3 month and I’m half way through the 4th month it doesn’t let me pull the correct data

3Month: Sum(IIf([Data_Field]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0))
 
6Month: Sum(IIf([Data_Field]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0))
 
9Month: Sum(IIf([Data_Field]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0))
 
12Month: Sum(IIf([Data_Field]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) 

Thank you for all you time and help
TCB

[ADezii]

The tricky part is that you are starting from the Last Day of the Previous Month and going backwards. Here is the required Logic, as I see it:

Dim dteDate As Date
 
dteDate = #12/28/2011#
 
Debug.Print "*************************************************************"
Debug.Print "Date:" & dteDate
Debug.Print "Last date of Previous Month: " & DateSerial(Year(dteDate), Month(dteDate), 1) - 1
Debug.Print "3 Months Prior to Last Day of Previous Months: " & _
             DateAdd("m", -3, DateSerial(Year(dteDate), Month(dteDate), 1) - 1)
Debug.Print "*************************************************************"

OUTPUT:

*************************************************************
Date:12/28/2011
Last date of Previous Month: 11/30/2011
3 Months Prior to Last Day of Previous Months: 8/30/2011
*************************************************************