getting a #error from count(iif( formula

[vickan240sx]

Hi,



I'm trying to get a count(iif( based on specific criteria, but I'm coming up with a #Error.

My current formula is as follows:

=Count(IIf(Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])<[Month]-2 And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Year] And Month([LoanOperationsTrackingLog]![HideDate])>=[Month]-2 And Year([LoanOperationsTrackingLog]![HideDate])>=[Year] And [LoanOperationsTrackingLog]![LDDProcessorDocsSentOut]=0,0))

Basically, I am trying to get a count of all the loan requests received but not yet completed for the (2 months prior) month.

Here is what my table looks like:

	July	August	September
previous month carry over
received
completed
cancelled
carry over to next month

Scenario:

I receive 5 loan requests in June. I do not complete any loan requests at all. In September, I decide to cancel a loan request.

My previous formula:

=Count(IIf(Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])<[Month]-2 And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Year] And [LoanOperationsTrackingLog]![HideLoan]=0 And [LoanOperationsTrackingLog]![LDDProcessorDocsSentOut]=0,0))

With my previous formula, my table had a fault and looked like this:

	July	August	September
previous month carry over	4	4	4
received	0	0	0
completed	0	0	0
cancelled	0	0	1
carry over to next month	4	4	3

The problem with the table above was that I had received 5 requests in June. All 5 requests were carried over until September when 1 was cancelled in September. The record cancelled in September should not update any of the previous months, but rather later months only.

The table should actually just look like this:

	July	August	September
previous month carry over	5	5	5
received	0	0	0
completed	0	0	0
cancelled	0	0	1
carry over to next month	5	5	4

The following months will look like this:

	August	September	October
previous month carry over	5	5	4
received	0	0	0
completed	0	0	0
cancelled	0	1	0
carry over to next month	5	4	4

Any help with my formula will be greatly appreciated. I've been puzzled over this for the past 3 days...and I think I'm going insane...I've gotten the entire report to work except for this last field. The table is dynamic and updates based on the current month and year that I enter. It draws numbers from whatever month I want and the 3 months on the table are also updated based on the current month/year that I choose.

[John_G]

Hi -

Your IIF statement is incorrect - it is missing the "value if false" part. Putting a few line breaks in to make it easier to read, it looks like this:

=Count(IIf(
Month([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])<[Month]-2
And Year([LoanOperationsTrackingLog]![LDDLogProcessingDateTime])=[Year]
And Month([LoanOperationsTrackingLog]![HideDate])>=[Month]-2
And Year([LoanOperationsTrackingLog]![HideDate])>=[Year]
And [LoanOperationsTrackingLog]![LDDProcessorDocsSentOut]=0,0))

The 0 is the value to return if the expression is true - but you have not entered a value to return if the expression is false.

And what is it you are trying to count? I use A2003, and it does not have a count() function (I don't know about A2007 or A2010) - that may be the cause of the #Error message.

John