Iff Statement -- I need it to do two different things...

[LanieB]

So I have MANY reports that are showing scores for what our employees got on their audits... my question is...

Sometimes the points are N/A -- but I do not know how to tell access to show me N/A when it see 0/0 but show 0% when it sees 0/10. Right now I have it as...

Overall Score: IIf([Points Available]=0,0, [Points Earned]/[Points Available])
I had to write that because it would give me an error when it tried to divide anything by 0. But the problem is on their reports sometime there was 0 available and 0 earned and it still shows up as a 0%. Is there any iff statement in the world of access that can recognize 0/0 = N/A but 0/any number = 0%?

[ssanfu]

Will this work?

IIf([Points Earned]=0 AND [Points Available]=0,"N/A",IIf([Points Available]=0,0, [Points Earned]/[Points Available]))

[LanieB]

Thank you!! It does work But now I am unable to set the format to show as a percentage. Is there another way I would be able to display the results in percent form?

[LanieB]

I tried to set the format in both the query and report. But neither will work.

[jzwp11]

Since the function can return text ("N/A") or a number that is why you cannot apply a format.

One alternative is to not return "N/A", but rather just an empty field (NULL). With that you will be able to apply the format when a numerical value is returned.

IIf([Points Earned]=0 AND [Points Available]=0,NULL,IIf([Points Available]=0,0, [Points Earned]/[Points Available]))

[LanieB]

You are my favorite person in the whole world...

THANK YOU SO MUCH!

[jzwp11]

You're welcome!

[ssanfu]

Hey, what about me???

I did all the heavy lifting (or was it thinking?)..

jzwp11 just made it work when I wasn't looking...



OK, here is the best of both worlds:

IIf([Points Earned]=0 And [Points Available]=0,"N/A",IIf([Points Available]=0,0,Format([Points Earned]/[Points Available],"percent"))

[jzwp11]

Steve,

I was messing around with the format() function in an attempt to keep the "N/A" but was unsuccessful. I got that one figured out.

Thanks!

[ssanfu]

Steve,

I was messing around with the format() function in an attempt to keep the "N/A" but was unsuccessful. I got that one figured out.

Thanks!

I used an three unbound controls on an unbound form and it seemed to return the correct values....

time

[jzwp11]

I was just not using the format() function correctly; I do not use it much in my applications.