IFF Statement in Report

[Guest]

I need help on IIF Statement in a report. I think if one is created I can
change it for multiple uses.

I have 3 choices for an option group on a form that are 'Service Calls',
'Agency Calls' and 'Other'. Each of these could be done by 'Physical Call'
or 'Phone Call' which is a option group as well.

How do I write the IIF Statement to count the number of Physical Calls and
Phone Calls for each of the 3 choices. This is where I think one statement
you can help with could be changed for each of the 3.

Table on report will look like when working!
Physical Calls Phone Calls
Service Calls # #
Agency Calls # #
Other # #

 

[Arvin Meyer]

Try something like this:

SELECT Calls, Sum(IIf([Calls]="Physical Calls",1,0)) AS Expr1,
Sum(IIf([Calls]="Phone Calls",1,0)) AS Expr2
FROM Table1
GROUP BY Calls;

Since Option Groups store integers, you'll need to substitute a string for
each value. You can do that with the Switch function, or with a nested IIf
function, or you can just use the correct labels on your report.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Arvin forget my post.

I solved my issue with

=Sum(IIf([Call Type]="Service" And [Type of Service]="Physical Call",1,0)

Arvin,

I think it can be simpler. I use the query to count both [Call Type] which
is 'Service Calls', 'Agency Calls' and 'Other' as well as [Type of Service]
which is either 'Physical Call' or 'Phone Call'. So I have countof for both
these fields.

Isn't there a better IFF statment to use to count each one on its own? I
would an IIF statement to tell me the number of Physical Calls for Service
Calls for example.

Thank you.

Try something like this:

SELECT Calls, Sum(IIf([Calls]="Physical Calls",1,0)) AS Expr1,
Sum(IIf([Calls]="Phone Calls",1,0)) AS Expr2
FROM Table1
GROUP BY Calls;

Since Option Groups store integers, you'll need to substitute a string for
each value. You can do that with the Switch function, or with a nested IIf
function, or you can just use the correct labels on your report.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I need help on IIF Statement in a report. I think if one is created I can
change it for multiple uses.

I have 3 choices for an option group on a form that are 'Service Calls',
'Agency Calls' and 'Other'. Each of these could be done by 'Physical Call'
or 'Phone Call' which is a option group as well.

How do I write the IIF Statement to count the number of Physical Calls and
Phone Calls for each of the 3 choices. This is where I think one statement
you can help with could be changed for each of the 3.

Table on report will look like when working!
Physical Calls Phone Calls
Service Calls # #
Agency Calls # #
Other # #