Please help with this formula.

[vickan240sx]

Hi,



I'm unable to get this formula to work.

 =Count(IIf(([LoanOperationsTrackingLog]![NewRenewalExtensionModification]="New" And [LoanOperationsTrackingLog]![LoanTypeCodeLDD]="Consumer - Non TCD" And Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Forms]![LDDProductionRptDateSelection]![Month] And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Forms]![LDDProductionRptDateSelection]![Year]),0))

When I use just the month field, it works and vice versa for the year field. However, when I try to use both at the same time, i get an error.

Any help is greatly appreciated.

Thank you

[June7]

What is the error?

Want to provide db for analysis? Follow instructions at bottom of my post.

[vickan240sx]

The error I get on my report just says #Error. I can't attach the database, because I have about 18 tables and about 50 forms that would need to be cleaned up prior to attaching it; the database is also 7mb too.

=Count(IIf(([LoanOperationsTrackingLog]![NewRenewalExtensionModification]="New" 
And [LoanOperationsTrackingLog]![LoanTypeCodeLDD]="Consumer - Non TCD" 
And Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Forms]![LDDProductionRptDateSelection]![Month] 
And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Forms]![LDDProductionRptDateSelection]![Year]),0))

The breakdown of my code is as follows:

I'm trying to count the number of occurrences of certain records meeting all the criteria:

1) Month = Month input into search form (I input the #9)
2) Year = Year input into search form (I input #2012)
3) Criteria 1 = New
4) Criteria 2 = Consumer - Non TCD

[vickan240sx]

I figured it out with your help from a related thread.

=Count(IIf(Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Month] 
And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Year] 
And [LoanOperationsTrackingLog]![LoanTypeCodeLDD]="Consumer - Non TCD",
[LoanOperationsTrackingLog]![NewRenewalExtensionModification]="New"))

Thank you x2!