Date Formula Help

[TJVFree]

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.

 

[dhookom]

Your IIf() statement would need to use between two dates rather than just >.


Duane
Hook'D on Access
MS Access MVP

 

[alvechurchdata]

I agree that the > looks wrong and I'd expect to be looking for the four quarterly totals. It depends what the original spec asked for, the users might be wanting totals for the last 3, last 6, last 9 and last 12 months.

Geoff Franklin

http://www.alvechurchdata.co.uk