CountIIF - Counting Date Values - CountIF

[Gordon]

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

 

[boblarson]

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

 

[Gordon]

=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

 

[boblarson]

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.

 

[Gordon]

Sum and Count for selective Counting of Records -- CountIF

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

 

[Gordon]

Format date for selective criteria query

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

 

[Brianwarnock]

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

 

[boblarson]

Sum should work for you as well.

 

[Gordon]

Thanks.... Countif Sum Records with Date

Sum should work for you as well.

Thanks everyone. Very helpful posts.....

Gordon

 

[Brianwarnock]

Sum should work for you as well.

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

Brian

 

[rachel_fo]

My problem is similar. I am creating a query, which I will then create a report from (unless there is an easier way- I'm an Access noob).

I'm trying to create a report where it will count how many instances/rows occur in a date range recorded in a field (DateofEvent) in each record. I also want it to sum a different field (NumberSpokenTo).

On the report, I'd like it to break down progress by week looking like the following:
Week Range # of Events # Spoken To
May 26-June 1 5 400
June 2-June 8 4 150

Any help appreciated. Please don't assume I know what I'm doing at all in your response!