Divide by Zero error module

[gdesreu]

Does anyone have a module that will work like excels iferror for access?
The iif statements for this are getting cumbersome for many of my calculations and I would prefer something like iferror([field1]/[field2],null) and NOT calculation: IIf([field2]=0,Null,[field1]/[field2]). This of course is a simple calculation and not representative of the scientific calcs I deal with day in and day out.
Thanks

 

[Joe4]

First, to address the question about passing data to a function from a form control: that is the simplest use; passing an object like the control itself is no more difficult, it's just a matter of writing the function to expect one or the other (or even either if necessary

That isn't what I was getting at. I was referring to passing in a whole expression for the function to evaluate, rather than have the calculation built by the function.
What you suggested is pretty straightforward, and I already did something like that above.

I am talking about trying to create a true IFERROR function that would handle any expression you pass into it.
So, for sake of an example, let's say it was this simple division and the expression is:

[Field1]/[Field2]

So, much like Excel's IFERROR works, we would want to just wrap the whole expression in IFERROR, i.e.

IFERROR([Field1]/[Field2],0)

So, the IFERROR UDF would have to evaluate whatever expression is passed in [Field1]/[Field2]
What I was questioning is this, if you were to try to setup a UDF in this manner, how does the expression recognize that Field1 and Field2 are variables, and what there value is in that particular instance?
I am not sure it could be done in this manner.

 

[Micron]

AFAIK, a udf has to be structured something like Function MyFunction (someParam As someType) As SomeType (the part outside the brackets if we want the function to return something? So if you are asking if it can appear and work like a built in function like IsError I'd say no it can't.

Yes the function could accept the expression as a string - [Field1]/[Field2] if that made sense, but at the moment I see no real value in that approach. The value would be in allowing the function to handle the error. If the OP wants a function that can evaluate several conditions like IsError can (such as #Name, #Num, #Value, #Ref) there would have to be an associated error number for each and it would have to be a run-time error. While some/all of these are also errors in Access, they're not run time errors. That alone would make it impossible to have the exact equivalent error function in Access. If one opens a form and a control cannot resolve its data reference, you are likely to see #NAME in the control, but no trappable error is generated. Maybe I'm still not getting your drift. If so, I apologize. As for not being the first to post a function, I believe I pointed that out in my post. Anyway, I see that your post solved the issue.

 

[Joe4]

Yeah, I don't think we are on the same page here.

The functions you and I created are specific calculations that handle specific errors. What I was interested in doing (or trying to see if I could do) was create a function like the the Excel IFERROR function. I assume that you are familiar with how that works. It works on any calculation (not a specific pre-defined function), and returns some value you tell it if it encounters any error, regardless of what that error may be.

So, in a nutshell, the question I was posing is can you create a UDF in Access VBA that does what the Excel IFERROR function does (and handle any error from any calculation you feed it)?
I don't know if it possible. If it is, it is probably beyond my abilities.

 

[Micron]

A little bit familiar with it - as far as Google permits. We might be on the same page without realizing it. It's just that the function I posted was limited to division by zero as written (as was, I think, your accepted solution) and as I noted, but I did try to imply that it was an example only. To try to trap other errors one simply had to add to it. In my last post, I'm trying to say that if there's no correlation between an Access and Excel error, then there's no replication of the Excel function that can be had in Access. If it's possible, I don't know how either, but based on my previous comments about run time and comparable errors between the two applications, I think that's moot. Best part of all this is the OP seems happy for the moment...

 

[Joe4]

Yes, our functions are specific to a certain calculation (division by zero), and the calculation is explicitly spelled out right in the function.
I was trying to see if we could develop a generic IFERROR function, where the formula is fed into it, and it handles any error.
Are you familiar with the Excel IFERROR function? It would work much like that. Just don't know if it is possible.

 

[gdesreu]

A little bit familiar with it - as far as Google permits. We might be on the same page without realizing it. It's just that the function I posted was limited to division by zero as written (as was, I think, your accepted solution) and as I noted, but I did try to imply that it was an example only. To try to trap other errors one simply had to add to it. In my last post, I'm trying to say that if there's no correlation between an Access and Excel error, then there's no replication of the Excel function that can be had in Access. If it's possible, I don't know how either, but based on my previous comments about run time and comparable errors between the two applications, I think that's moot. Best part of all this is the OP seems happy for the moment...

Yes, I think you guys have satisfied what I need to know, which is, the iferror function is definitely beyond my capabilities at the moment. That in itself is helpful to know. An iferror function in access would probably benefit a great deal of people however if it difficult or if we are unsure it can be done, then Ill take the divide by zero function provided earlier as every little bit helps when you are under the gun to get data out the door by 5. The benefit of the iferror from my chair is, that I can simply write the forumula once verify it, then just add the iferror. Very simple. If I add iif statement, iserrors, (NZ[field] etc., it often exceeds the formula limit and I have to jump through hoops just to report a null in place of a div/0 error.
Thanks guys, if you can get an iferror function working, I would be most impressed/thankful along with many people around the planet dealing with this issue. It almost as painful as excel turning my data into dates when they aren't dates, but that's another topic.