IFERROR function equivalent in Access (1 Viewer)

[Hagridore]

I have a database which, among other things, tracks specific actions over time. Within this database, I have pages that run percentile statistics comparing specific actions (noted here for privacy as Action1 and Action2), within specified years. When there are records where both types of actions have taken place in the specified year, the queries and reports work flawlessly. But I am designing the database to be ready to accommodate years where there have been no actions as of yet. This is resulting in the usual error message one gets when one attempts to divide by zero. I'd like to be able to use the IFERROR function.

I did a Google search regarding the IFERROR function in Access and, not surprisingly, was directed to a question in this forum. Unfortunately, the question is from 2011, so I'm not just replying to it here. I tried applying what I found and am not getting what I want. The responder to the 2011 question indicated that the best way to do this was to use a combination of the IIf function and Iserror function. I attempted to follow their guidance and came up with the following:

=IIf(IsError([txt#ActualAction1s]/[txt#ActualAction2s]*100),"",txt#ActualAction1s]/[txt#ActualAction2s*100))

I'm getting a notice that there's an error in my expression syntax. Can someone help?

 

[pbaldy]

If that's a copy/paste, you having missing brackets in the second part.

 

[Galaxiom]

Two missing brackets and too many closing parentheses.

=IIf(IsError([txt#ActualAction1s]/[txt#ActualAction2s]*100),"",[txt#ActualAction1s]/[txt#ActualAction2s]*100)

If you avoid special characters in the object names you wouldn't need the brackets.

 

[Hagridore]

Wow...once again I'm ecstatic about how quick you folks are to reply. Thanks a bunch!

I corrected the issues with brackets and closing parentheses, and still end up getting my error message like I was getting before (#Num!). Here's the updated formula:

=IIf(IsError([txt#Action1s]/[txt#Action2s]*100),"",[txt#Action1s]/[txt#Action2s]*100)

The special characters were added by the system...the actual text box names are txtAction1s and txtAction2s (modified for privacy/confidentiality).

And while I'm in the mood for thanks...thanks for being gentle about the brackets and parentheses errors in the first place. That'll teach me to write "code" when I'm sleepy enough to drop off any second.

 

[jdraw]

Are you telling us that your field/control names are
txtAction1s and txtAction2s
and Access added the #?

First thing I'd do is remove the "#", or track down why it's there. This isn't normal behavior.

 

[Hagridore]

Wow...that was interesting. I went back in and looked at my textbox names and discovered that at some point I may have decided to use those symbols to further describe what's in the textboxes themselves. I have changed all the names to eliminate those symbols. I must've been asleep when I did it in the first place. I agree...it's not a good idea to use them, even if they don't have a direct effect, since they are used in programming. My formula, which still produces the error #Num!, is:

=IIf(IsError([txtAction1s]/[txtAction2s]*100),"",[txtAction1s]/[txtAction2s]*100)

Thanks for the best-practices heads up!

 

[jdraw]

I don't think IsError is doing what you think, but perhaps you could tell us what you think your code is doing/intended to do.

see http://stackoverflow.com/questions/22670418/iif-iserror-function-still-returning-error

 

[Hagridore]

I'm wanting to hide the error code that results from creating the report this comes from for those years where actions and records haven't been recorded/entered yet. As it is now, since the Action2 will be empty, I get the #NUM! error message. I'd like not to see that.

 

[CJ_London]

apart from anything else, if there is an error, you are returning a text value ("") and if not you are returning a number - bound to cause problems further down the line. Suggest return either 0 or null if there is an error if you want a numeric type to the column.

If your concern is that [txtAction2s] is either null or 0 then try

=IIf(nz([txtAction2s],0),null,[txtAction1s]/[txtAction2s]*100)

 

[Hagridore]

I tried the formula above using nz, but I'm still getting the same result.

 

[CJ_London]

looks like I missed a bit off

=IIf(nz([txtAction2s],0)=0,null,[txtAction1s]/[txtAction2s]*100)

 

[Hagridore]

looks like I missed a bit off

=IIf(nz([txtAction2s],0)=0,null,[txtAction1s]/[txtAction2s]*100)

This was EXACTLY what I was looking for. Thank you so much. It's all working exactly how I wanted it to. I'm looking for the place to mark this as "solved" but haven't found it yet. Again, thank you!!!!

 

[jeff3457]

looks like I missed a bit off

=IIf(nz([txtAction2s],0)=0,null,[txtAction1s]/[txtAction2s]*100)

Is there a way to add an OR type statement to this? Something along the lines of =IIf(nz([txtAction2s],0)=0 OR nz([txtAction1s],0)=0 ,null,[txtAction1s]/[txtAction2s].

I am working with a similar statement but I have the possibility of either number being 0

 

[pbaldy]

What you posted looks valid. Did you try it?

 

[Isaac]

I agree - it looks valid. But do add a closing parenthesis

 

[pbaldy]

But do add a closing parenthesis

Oops, didn't notice that.

 

[Isaac]

Oops, didn't notice that.

Slim pickins' on new questions, so I was digging LOL

 

[strive4peace]

with IIF, both arguments must be valid ... so even though the expression wouldn't be used if nz(txtAction2s,0) results in zero, perhaps do this in the expression:
... / nz( [txtAction2s],1)
even though that answer wouldn't be returned ... then it won't throw an error

 

[pbaldy]

Slim pickins' on new questions, so I was digging LOL

I know what you mean.

 

[Micron]

But do add a closing parenthesis

What am I missing? I see 2 lefts and 2 rights, and they appear to be in the correct places? Perhaps it was edited when I wasn't looking.

=IIf(nz([txtAction2s],0)=0,null,[txtAction1s]/[txtAction2s]*100)