countif function in Access

[Lifeseeker]

Hi,

It seems that Access doesn't have countif function.

I would like you guys to take a look at the example below and see if it is right or wrong:

So basically I need a count of all the cases where the time difference is less than 48 hours. Code is as follows:

select count((iif(datediff("h",[start date], [abc_result_time])<= 48,1,0))) as abc_result_time_cases
from tbl_abc;

Will this pick up all the "1" cases where the time difference is below 48 hours?

Thank you

 

[John Big Booty]

It appears you are doing this in a query, so first you could create a query with an expression using the DateDiff() function and then put your Criteria of <48 hours under that. Now use the results of that query in a second query where you can use the Totals function to count the results.

 

[plog]

Yes, COUNT will include all records no matter their value (1 or 0). SUM will effectively only include the records that equal 1. So, if you change it from COUNT to SUM you can get it all in one query:

SELECT SUM((iif(datediff("h",[start date], [abc_result_time])<= 48,1,0))) AS abc_result_time_cases
FROM tbl_abc;

Also, you stated that you wanted records less than 48 hours, but your query uses <= which means less than or equal to. If the value is 48 it will be included in the results. If 48 should be excluded, change the <=48 to <48.