Countif

P

profxavier27809

Registered User.

Local time

Today, 00:21

Joined

Sep 8, 2005

Messages

11

• Jan 11, 2006

• #1

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

Registered User.

Local time

Today, 05:21

Joined

Oct 5, 2004

Messages

749

• Jan 12, 2006

• #2

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

Code:

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

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

 

R

Rich

Guest

• Jan 12, 2006

• #3

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

 

J

jsparker

Sr. Data Technician

Local time

Yesterday, 23:21

Joined

Jun 27, 2008

Messages

24

• Jun 29, 2009

• #4

reclusivemonkey said:

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

Code:

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

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

Click to expand...

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?

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 16:21

Joined

Jan 20, 2009

Messages

12,882

• Jun 30, 2009

• #5

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.

 

J

jsparker

Sr. Data Technician

Local time

Yesterday, 23:21

Joined

Jun 27, 2008

Messages

24

• Jun 30, 2009

• #6

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.

 

M

map816

Registered User.

Local time

Today, 00:21

Joined

Sep 16, 2010

Messages

16

• Oct 4, 2010

• #7

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?