5

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kris
  • 83
  • 1
  • 2
  • 8
  • Surely that would only count it if it was the word "Housing" only, not if it was in it.. – BugFinder Jul 02 '12 at 14:17
  • well my intention is for it to count the amount of entries made for Housing, there are other services too such as Environmental Health and such. so in this example i would only want it to count housing but its not playing game. i dont understand the "Not if it was in it" part. as explained i am not very good at SQL coding and all that lark – Kris Jul 02 '12 at 14:27

2 Answers2

13

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))
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • superb, thank you! im not sure what the etiquette is and whether i need to ask another question for this, but basically these entries on the database are for people that have made mistakes in certain services that they are trained in at my work. not everyone is trained in the same services and this is to show how many "feedback entries" there are for each person in each service. the report asks the persons name and a date period, and outputs the entries that have been made. is there a way of this only showing and populating the service that have been entered for that person? – Kris Jul 02 '12 at 15:03
  • 1
    You can add additional criteria using `AND` and `OR`. For example, `=Sum(IIf([Service] = "Housing" AND [EmployeeName] = "John"))`. However, since you are doing this in a report, it would be better to filter the report itself when you open it to only show certain records. Check out the help for `DoCmd.OpenReport`. If you still have trouble, it's probably best to ask a separate question. – mwolfe02 Jul 02 '12 at 15:12
2

This also works:

=Count(IIf([Service]="Housing",1))
ib11
  • 2,530
  • 3
  • 22
  • 55
David Newton Simiyu
  • 21
  • 1