I've read various posts, some of which indicate that the CountIF function does not exist, and some of which suggest using it.

What I'm trying to do is to show the number of incidents in a report where the date in the field "Current Revision Date" is greater than a given date.

Here are the formulas I've tried:

=Count(IIf([Current Revision Date]>5/1/2007,1,0))

I've also tried:
=Count([Current Revision Date]>5/1/2007)

The result I get is a count of all of the records, not just the records whose date is greater than 5/1/2007

Any suggestions?

Thanks.....

Gordon

=Count(IIf([Current Revision Date]>#5/1/2007#,1,0))

=Count(IIf([Current Revision Date]>#5/1/2007#,1,0))

Hi Bob,

Thanks so much for the quick response. I tried your suggestion, but am still getting all 36 records. I should only be getting about 16 records. :(

Any other thoughts?

Gordon

Is your date field a date/time field (with time in it as well)? I've had that problem before so I created a separate date field in my query to be just the date.

I found the following suggestion in another Thread:

"Count gives a total count of all the records, which is what it is supposed to do.

Sum gives a total count of all the records meeting the criteria, it Sums the result of the IIf statement."

I tried changing the formula to

=Sum(IIf([Current Revision Date]>#5/1/2007#,1,0)) and it returns the correct result.

Does anyone see any problem with using "Sum" instead of "Count"?

Gordon

Is your date field a date/time field (with time in it as well)? I've had that problem before so I created a separate date field in my query to be just the date.

Good suggestion... but the format is "Short Date" (D/mm/yy)

Gordon

I found the following suggestion in another Thread:

"Count gives a total count of all the records, which is what it is supposed to do.

Sum gives a total count of all the records meeting the criteria, it Sums the result of the IIf statement."

I tried changing the formula to

=Sum(IIf([Current Revision Date]>#5/1/2007#,1,0)) and it returns the correct result.

Does anyone see any problem with using "Sum" instead of "Count"?

Gordon

No the use of Sum is correct, the IIf returns 1 when the criteria is met and you want to sum those 1s

Brian

Sum should work for you as well.

Sum should work for you as well.


Thanks everyone. Very helpful posts.....

Gordon

Sum should work for you as well.

It is not a case of as well, Sum is the correct and only function.

Brian