IIF statement help

Hi all,
For some reason I am receiving an error when parsing this statement:
=iif(sum(fields!Sales.value)=0,0,sum(fields!Price.value)/sum(fields!Sales.va
lue))
So the % works however as some of the sales do equal 0 I receive errors on
the report... Why won't the IIF statement place a 0 there as requested in
this situation?
Help!AshVsAOD,
You may have to return a string and then convert it back to numeric...
=CCur(iif(sum(fields!Sales.value)=0,"0",sum(fields!Price.value)/sum(fields!Sales.va
lue)))
I did not test this statement but I have run into similar things before.
"AshVsAOD" wrote:
> Hi all,
> For some reason I am receiving an error when parsing this statement:
> =iif(sum(fields!Sales.value)=0,0,sum(fields!Price.value)/sum(fields!Sales.va
> lue))
> So the % works however as some of the sales do equal 0 I receive errors on
> the report... Why won't the IIF statement place a 0 there as requested in
> this situation?
> Help!
>
>|||I think the IIF statement calculates all branches of the if when executed
thus giving a divide by zero error.
Try using the swich command.
=CCur(switch(sum(fields!Sales.value)=0,0,true,
sum(fields!Price.value)/sum(fields!Sales.value)))
"williamericnichols" wrote:
> AshVsAOD,
> You may have to return a string and then convert it back to numeric...
> =CCur(iif(sum(fields!Sales.value)=0,"0",sum(fields!Price.value)/sum(fields!Sales.va
> lue)))
> I did not test this statement but I have run into similar things before.
>
>
> "AshVsAOD" wrote:
> > Hi all,
> >
> > For some reason I am receiving an error when parsing this statement:
> >
> > =iif(sum(fields!Sales.value)=0,0,sum(fields!Price.value)/sum(fields!Sales.va
> > lue))
> >
> > So the % works however as some of the sales do equal 0 I receive errors on
> > the report... Why won't the IIF statement place a 0 there as requested in
> > this situation?
> >
> > Help!
> >
> >
> >|||Thanks guys...
The IIF statement does indeed calculate both branches...
Cheers
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:4E854AE3-B7AF-4572-AA69-355F8A1F076A@.microsoft.com...
> I think the IIF statement calculates all branches of the if when executed
> thus giving a divide by zero error.
> Try using the swich command.
> =CCur(switch(sum(fields!Sales.value)=0,0,true,
> sum(fields!Price.value)/sum(fields!Sales.value)))
> "williamericnichols" wrote:
> > AshVsAOD,
> >
> > You may have to return a string and then convert it back to numeric...
> >
> >
=CCur(iif(sum(fields!Sales.value)=0,"0",sum(fields!Price.value)/sum(fields!S
ales.va
> > lue)))
> >
> > I did not test this statement but I have run into similar things before.
> >
> >
> >
> >
> > "AshVsAOD" wrote:
> >
> > > Hi all,
> > >
> > > For some reason I am receiving an error when parsing this statement:
> > >
> > >
=iif(sum(fields!Sales.value)=0,0,sum(fields!Price.value)/sum(fields!Sales.va
> > > lue))
> > >
> > > So the % works however as some of the sales do equal 0 I receive
errors on
> > > the report... Why won't the IIF statement place a 0 there as requested
in
> > > this situation?
> > >
> > > Help!
> > >
> > >
> > >