CountIf formula within MS Access report

sql ms-access report ms-query
Kris picture Kris · Jul 2, 2012 · Viewed 57.1k times · Source

Something terrible as happened with my poor attempt at adding a CountIf forumula to my access report.

I am trying to add a formula within my report header to count the number of entries in my Service column that contain a certain word. The problem is I am an absolute amateur when it comes to SQL code. 

=CountIf([Service]="Housing")

Is the code I was hoping would work but I don't seem to be getting anywhere.

Answer

mwolfe02 picture mwolfe02 · Jul 2, 2012

There is no Countif function in Access. However, you can use Sum and IIf (ie, Immediate If) to accomplish the same thing. Try:

=Sum(IIf([Service] Like "*Housing*", 1, 0))

The above assumes you want the Service column to contain the word "Housing". This assumes you were being precise in the wording of your question. If you really meant that you want to count the number of records where the Service column equals "Housing" exactly, you would use this instead:

=Sum(IIf([Service] = "Housing", 1, 0))

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More