Count occurences of text in a query

[funkygoorilla]

Hi,


I am running a totals report from a couple different tables. Most of my data is numeric, which is easy to total, but one of the totals I need is text. The text is called Sight Action, which for each entry the users would make, they would choose from a list that includes Sight Action amongst other choices. I need to be able to total up how may times in a month, the option Sight Action had been chosen. The table is called tblCallType, with Radio Call, Sight Action, Assist, and N/A as option. When the user enters a record, they have to pick one of these options. The only one I need to report on is Sight Action, but I cant figure out how to total that in a query. For example, if the user enters 100 records for the month of February, and 25 of those records were marked with Sight Action, I need 25 to show on the report. I tried the Count function, but it returns a number that isnt the total and I cant figure out what it could be totaling. Any help would be greatly appreciated.

[pbaldy]

Try

Sum(IIf(FieldName = "Sight Action", 1,0))

You could also use a DCount() in the report.

[funkygoorilla]

Hi, thanks for the reply. I think more what I am trying to do is total the amount fo times Sight Action has been selected and put that total in a report. I have my query built which has all of the totals for the numeric data I need. I know how to build the query, but is it possible for this specific field, to just total the amount of time Sight Action is entered?

[pbaldy]

Yes, I know. Did you try it?

[funkygoorilla]

I did try it but I get an error. Do I just put it in a new column in the query?
Thats what I tried and I get an "The expression you entered contains an invalid syntax."
This is what I used in a new column,
SightAction: Sum(IIf(Call Type = "Sight Action", 1,0))

[pbaldy]

Because of the inadvisable space, you need to bracket the field name.