Countif

[profxavier27809]

Hey there,

Is there a quick expression to put in the control source area of a field on a report that says COUNTIF([Sample Text Field] = "NO")

I would like it to only count the occurrences of NO.

 

[reclusivemonkey]

There is no COUNTIF in access, but you could put a field on your report which has

IIF([Sample Text Field]="NO",1,0)

make it invisible, then just use COUNT to get the number of occurences.

 

[Rich]

=Sum(Iif([YourField]=No,1,0))

 

[jsparker]

There is no COUNTIF in access, but you could put a field on your report which has

IIF([Sample Text Field]="NO",1,0)

make it invisible, then just use COUNT to get the number of occurences.

I understand how to make the above text box invisible. My field is called [Error Count] but when I try to add another field to COUNT[Error Count] it isn't recognizing the field and zeroing it out when I run the report. I must be missing something.

Help?

 

[Galaxiom]

Although the new text box with the IIF statement will show the result for each record you cannot perform Totals calculations on the box.
Totals are performed on fields in the Record Source.

You need to add the IIF expression as a field in the Record Source query of your report.

Also you will want to SUM the [Error Count] field to get the total of "NO" values.
COUNT will just give you the total number of records regardless of their value.

You do not need to include the [Error Count] textbox on the report at all.
It just needs to be a field in the Record Source.

 

[jsparker]

Thanks Galaxiom. That was my issue. I kept trying to sum at report level and it was giving an error message. Once I plugged in the formula at the record source level and then just summed at report level it worked perfectly. Thanks again.

 

[map816]

Can you use this for a field that is concatenated too? You can in excel. I did this in access and it will return a 1 for those fields with only the text option in it but if it had the text option I was looking for and then other options in the field with it it returns a 0 for that field when I was hoping for a 1 too since the text was in the field. In excel you can just add "*text*" and it will count the text but it doesn't seem to work in access. Suggestions?