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]

There is no IFERROR in Access. There is an ISERROR, similar to the ISERROR in Excel, so you could do a IIF(ISERROR(...
but that probably isn't much shorter than what you are doing now.

There is also a NZ function that can be used to handle Null values.
See: https://www.techonthenet.com/access/functions/advanced/nz.php

Quite frankly, if I had a complex Access function that needed to check a bunch of values, I would be tempted to use VBA to create my own function.
I don't like long messy functions in Access, they can be difficult to maintain.

 

[gdesreu]

There is no IFERROR in Access. There is an ISERROR, similar to the ISERROR in Excel, so you could do a IIF(ISERROR(...
but that probably isn't much shorter than what you are doing now.

There is also a NZ function that can be used to handle Null values.
See: https://www.techonthenet.com/access/functions/advanced/nz.php

Quite frankly, if I had a complex Access function that needed to check a bunch of values, I would be tempted to use VBA to create my own function.
I don't like long messy functions in Access, they can be difficult to maintain.

Thanks but it is a function I am looking for. ISerrors and NZ get crazy long in some of my calcs and it gets exponentially worse when others enter calculations in the database that don't work correctly. Most people do ok with the actual formulas but not with the trapping of the divide by 0. Id prefer to handle it through a function like the iferror.
If anyone has a function for this please let me know thanks.

 

[Norie]

How would this function work?

What inputs would it take?

What would it output?

What would determine what it output?



PS This IIf will fail if [Field2] is 0, both the True and False part of an Iif are always calculated.

IIf([field2]=0,Null,[field1]/[field2])

 

[gdesreu]

How would this function work?

What inputs would it take?

What would it output?

What would determine what it output?



PS This IIf will fail if [Field2] is 0, both the True and False part of an Iif are always calculated.

IIf([field2]=0,Null,[field1]/[field2])

Hi I want it to work exactly like it does in excel. For example: iferror([field1]/[field2],null)
Also the formula doesn't fail for me. It returns as follows:
1/2=.5
1/0 = null
1/null=null

 

[Norie]

So what exactly would be passed to the function?

Would it be the expression to be evaluated along with all the fields/values the expression requires?

 

[Joe4]

I thought about it, creating your own custom IFERROR function.
I supposed you could pass the entire function, and have the IFERROR UDF use EVAL to try to evaluate it, and if there is an error, return whatever you want returned.
But there is something that I am not sure how to get around. The formula being passed in will probably be using table/query field references. If that gets passed to the UDF, I am not sure it will evaluate because it probably won't know what it is.

For example, if you pass [Field1]/[Field2] as the formula to evaluate, I am not sure that VBA is going to know what to do with that, since the UDF itself is not tied to any data source.
Maybe there is a way to do it, but I am not aware of it.

Seems to me that if this is something that is coming up a lot, it may be indicative of a data control issue (too many zeroes/nulls in data may suggest the structure of the database is not optimized).

 

[gdesreu]

So what exactly would be passed to the function?

Would it be the expression to be evaluated along with all the fields/values the expression requires?

Im not sure. That's what Im asking for help on. I would assume the result of the calc, if its a divide by zero then do something other than throw the divide.

 

[gdesreu]

I thought about it, creating your own custom IFERROR function.
I supposed you could pass the entire function, and have the IFERROR UDF use EVAL to try to evaluate it, and if there is an error, return whatever you want returned.
But there is something that I am not sure how to get around. The formula being passed in will probably be using table/query field references. If that gets passed to the UDF, I am not sure it will evaluate because it probably won't know what it is.

For example, if you pass [Field1]/[Field2] as the formula to evaluate, I am not sure that VBA is going to know what to do with that, since the UDF itself is not tied to any data source.
Maybe there is a way to do it, but I am not aware of it.

Seems to me that if this is something that is coming up a lot, it may be indicative of a data control issue (too many zeroes/nulls in data may suggest the structure of the database is not optimized).

Unfortunately, its not a data control issue. This is environmental data, thousands of samples with hundreds of reported fields, some 0 and some null. If the data was collected 10 years ago, there is no way to resample something that is already past a holdtime. That's means the data is what it is and is factually correct. It then falls on me to sort out, typically with a 4 hr budget for 40hrs of work. There is ZEEERO tolerance for any errors either, so, an iferror function would be a great help over me scrutinizing hundreds of different formulas. I was hoping to avoid the paid add on route if possible.
Thanks

 

[Joe4]

I tried Googling to see if anyone had created a custom IFERROR function that could be used, but unfortunately I could not find any.
Every instance I came across either uses the methodology you are currently using, or the IIF(ISERROR(... functionality.