count iif

Hope someone can help.
I am trying to do a count where a field is equal to a certain number of
values.
The example below doesn't work but this is the idea.
=Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
Fields!JobNo.Value, Nothing))
I wondered if someone could point me in the right direction.
Thanks PaulSELECT SUM(CASE WHEN VisitType IN ('RTF', 'ARF', 'ERF', 'WOK') THEN 1 ELSE 0
END)
FROM your_table
--
Jacco Schalkwijk
SQL Server MVP
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:6D921197-A432-4C19-B5AE-1958B94CE795@.microsoft.com...
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Try the posted case statement first, but if that doesn't work, you have to
spell out your fields again.
Like:
=Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
Fields!JobNo.Value, Nothing))
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"pcalv" wrote:
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Thanks for the replies.
Catadmin i used your suggestion which worked great.
Cheers Paul
"Catadmin" wrote:
> Try the posted case statement first, but if that doesn't work, you have to
> spell out your fields again.
> Like:
> =Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
> or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
> Fields!JobNo.Value, Nothing))
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > I am trying to do a count where a field is equal to a certain number of
> > values.
> >
> > The example below doesn't work but this is the idea.
> >
> > =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> > Fields!JobNo.Value, Nothing))
> >
> > I wondered if someone could point me in the right direction.
> >
> > Thanks Paul|||Glad I could help. @.=)
Catadmin
"pcalv" wrote:
> Thanks for the replies.
> Catadmin i used your suggestion which worked great.
> Cheers Paul
>