If statement in report

S

Sprowler

I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
 
D

Dale Fye

Are you actually trying to do this in the report or in the reports query? Is
the field [qualified] actually a text field, or a Yes/No field?

1. If you are doing this in a query, and the [qualified] field is actually
a Yes/No data type, then it will have to be a group by query and the column
in the query grid would look something like:

NumQualified: ABS(SUM([Qualified]))

2. If you are doing it in a query, and the [qualified] field is text then,
try:

NumQualified: ABS(SUM(IIF([Qualified] = "yes", 1, 0)))

HTH
Dale
 
S

Sprowler

No, it's a calculated field that I am adding in a report.

Dale Fye said:
Are you actually trying to do this in the report or in the reports query? Is
the field [qualified] actually a text field, or a Yes/No field?

1. If you are doing this in a query, and the [qualified] field is actually
a Yes/No data type, then it will have to be a group by query and the column
in the query grid would look something like:

NumQualified: ABS(SUM([Qualified]))

2. If you are doing it in a query, and the [qualified] field is text then,
try:

NumQualified: ABS(SUM(IIF([Qualified] = "yes", 1, 0)))

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Sprowler said:
I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
Click to expand...
Click to expand...
 
S

Sprowler

No, it's a calculated field that I am adding into a report.

Dale Fye said:
Are you actually trying to do this in the report or in the reports query? Is
the field [qualified] actually a text field, or a Yes/No field?

1. If you are doing this in a query, and the [qualified] field is actually
a Yes/No data type, then it will have to be a group by query and the column
in the query grid would look something like:

NumQualified: ABS(SUM([Qualified]))

2. If you are doing it in a query, and the [qualified] field is text then,
try:

NumQualified: ABS(SUM(IIF([Qualified] = "yes", 1, 0)))

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Sprowler said:
I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
Click to expand...
Click to expand...
 
S

Sprowler

It is actually a calculated field that I am adding in a report. Suggestions?

Dale Fye said:
Are you actually trying to do this in the report or in the reports query? Is
the field [qualified] actually a text field, or a Yes/No field?

1. If you are doing this in a query, and the [qualified] field is actually
a Yes/No data type, then it will have to be a group by query and the column
in the query grid would look something like:

NumQualified: ABS(SUM([Qualified]))

2. If you are doing it in a query, and the [qualified] field is text then,
try:

NumQualified: ABS(SUM(IIF([Qualified] = "yes", 1, 0)))

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Sprowler said:
I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
Click to expand...
Click to expand...
 
J

John Spencer

Try

Count(IIF([Qualified]="Yes",1,Null))

or

Abs(Sum([Qualified]="Yes"))

That assumes the field is a text field and not a Yes/no (boolean) field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Sprowler

Thanks John. The second string work perfectly and made for an impressive
report. Thank you very much!

John Spencer said:
Try

Count(IIF([Qualified]="Yes",1,Null))

or

Abs(Sum([Qualified]="Yes"))

That assumes the field is a text field and not a Yes/no (boolean) field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sprowler said:
I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
Click to expand...
Click to expand...
 
A

akram

John Spencer said:
Try

Count(IIF([Qualified]="Yes",1,Null))

or

Abs(Sum([Qualified]="Yes"))

That assumes the field is a text field and not a Yes/no (boolean) field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sprowler said:
I am trying to put in an calculated field in my report. I have a field
called "qualified" and in the field I would like it to total the number of
"yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that
doesn't work. I think I am close. Does anyone have any suggestions.
Click to expand...

To John Spencer
Click to expand...
Thank you very much
the answer is very important and very useful.
programmer akram
iraq-basrah
 
Top