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

[TBC]

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.

• 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.
  [*]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))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]6Month: Sum(IIf([Data_Field]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]9Month: Sum(IIf([Data_Field]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]12Month: Sum(IIf([Data_Field]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0))

Thank you for all you time and help
TCB

 

[boblarson]

Past 3 months starting at the end of the prior month and go back 3 months.

3Month: Sum(IIf([Data_Field] Between DateSerial(Year(Date()), Month(DateAdd("m", -4, Date()), 1) And DateSerial(Year(Date()), Month(Date()), 0),[Amount_Actual_Loan],0))

Past 6 months starting at the end of the prior month and go back 6 months.

6Month:Sum(IIf([Data_Field] Between DateSerial(Year(Date()), Month(DateAdd("m", -7, Date()), 1) And DateSerial(Year(Date()), Month(Date()), 0),[Amount_Actual_Loan],0))

Past 9 months starting at the end of the prior month and go back 9 months.

9Months:Sum(IIf([Data_Field] Between DateSerial(Year(Date()), Month(DateAdd("m", -10, Date()), 1) And DateSerial(Year(Date()), Month(Date()), 0),[Amount_Actual_Loan],0))

Past 12 months starting at the end of the prior month and go back 12 months

12months:Sum(IIf([Data_Field] Between DateSerial(Year(Date()), Month(DateAdd("m", -13, Date()), 1) And DateSerial(Year(Date()), Month(Date()), 0),[Amount_Actual_Loan],0)).

 

[TBC]

Thanks Bob

 

[TBC]

Thanks again Bob, that worked perfect