Date Formula Help

[TBC]

I'm using the formula below to get my 3Month, 6Month, 9Month, and 12Month totals for the [Amount_Actual_Loan]. I don’t thing I'm getting the information I'm actually wanting
Please see below:

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

I think the query I wrote out in Plan English form is the way I need to be going.
Can someone please help me put this information below into a formula I can use to get my 3Month, 6Month, 9Month, and 12Month totals

3Month:Find all the dates from Jan 2010 to March 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and sum [Amount_Actual_Loan]
6Month:Find all the dates from April 2010 to June 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and sum [Amount_Actual_Loan]
9Month:Find all the dates from July 2010 to September 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and sum [Amount_Actual_Loan]
12Month:Find all the dates from October 2010 to December 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and sum [Amount_Actual_Loan]

And this one I would like to count the the CORRESPONDENT_PURCHASE_DATE]) that the [Amount_Actual_Loan] come in on

3Month:Find all the dates from Jan 2010 to March 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and count [Amount_Actual_Loan]
6Month:Find all the dates from April 2010 to June 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and count [Amount_Actual_Loan]
9Month:Find all the dates from July 2010 to September 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and count [Amount_Actual_Loan]
12Month:Find all the dates from October 2010 to December 2010 from field ([CORRESPONDENT_PURCHASE_DATE]) and count [Amount_Actual_Loan]

Thanks you so much for taking the time and help.

 

[Brianwarnock]

Try

6Month: Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-6,Date()) and [CORRESPONDENT_PURCHASE_DATE]<DateAdd("m",-3,Date()),[Amount_Actual_Loan],0))

Brian

 

[RainLover]

DatePart would be a better function to use rather than DateAdd.

Have a look at the help files.

 

[Brianwarnock]

DatePart would be a better function to use rather than DateAdd.

Have a look at the help files.

I was about to ask why but then read all of the first post rather than just the first bit, yes he actually wants specific months in the year, so why not just Between #01/01/2010# and #31/03/2010# etc
To make it available to any current year then use Dateserial.

Brian