CODEWITHSUNDEEP
sqlms-accessreportms-query
Kris
Kris

Reputation: 83

CountIf formula within MS Access report

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.

Upvotes: 5

Views: 78145

Answers (2)

David Newton Simiyu
David Newton Simiyu

Reputation: 21

This also works:

=Count(IIf([Service]="Housing",1))

Upvotes: 2

mwolfe02
mwolfe02

Reputation: 24207

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))

Upvotes: 14

Related Questions