IIF and ISERROR Synatx problems in ACCESS

[Chris]

Hi I have a formula that I am trying to remove any potential #Errors in
Access. I have looked at other postings online but can not find the
correct syntax to make my formula work.

I am basically trying to do:

IIF(ISERROR(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])),"
",
(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])))

I am having syntax problems with my parenthethis. This formula is an
example as I actually have 10 numerators and 5 denominators. Please
help. I can not just do an IIF statement =0 as this will only handles
0s and not errors. I need a nested ISERROR in an IIF.

Thanks,
Chris

 

[Guest]

I am assuming that numerator_1, ... numerator_n, and denominator_1, ...
denominator_n already contain numeric values that have been validated through
the data entry process. If thsi already true then you formula is invalid only
if the denominators total = zero because this is an invalid Math calculation.
(use your calcuator and try to divide by zero, you will get an error) What
you should check is: IIF(denominator_ +denominator_n=0,"", [Do your
calculations])

 

[Chris]

The numerators have not been individually evaluated for null values.
Most will have numbers but a few are nulls. That is why I am setting
up the ISERRO formula here. Add/Divide them if normal, ignore if
because there is a 0 in one of the component numbers causing an error,
then the iserror kicks in.

I am assuming that numerator_1, ... numerator_n, and denominator_1, ...
denominator_n already contain numeric values that have been validated through
the data entry process. If thsi already true then you formula is invalid only
if the denominators total = zero because this is an invalid Math calculation.
(use your calcuator and try to divide by zero, you will get an error) What
you should check is: IIF(denominator_ +denominator_n=0,"", [Do your
calculations])

Hi I have a formula that I am trying to remove any potential #Errors in
Access. I have looked at other postings online but can not find the
correct syntax to make my formula work.

I am basically trying to do:

IIF(ISERROR(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])),"
",
(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])))

I am having syntax problems with my parenthethis. This formula is an
example as I actually have 10 numerators and 5 denominators. Please
help. I can not just do an IIF statement =0 as this will only handles
0s and not errors. I need a nested ISERROR in an IIF.

Thanks,
Chris

 

[Guest]

Chris,
Is a null value different from having a value of zero for your calculation
purposes? For example: if null value then divide by 3 but if value is zero
then divide by 4.

The numerators have not been individually evaluated for null values.
Most will have numbers but a few are nulls. That is why I am setting
up the ISERRO formula here. Add/Divide them if normal, ignore if
because there is a 0 in one of the component numbers causing an error,
then the iserror kicks in.

I am assuming that numerator_1, ... numerator_n, and denominator_1, ...
denominator_n already contain numeric values that have been validated through
the data entry process. If thsi already true then you formula is invalid only
if the denominators total = zero because this is an invalid Math calculation.
(use your calcuator and try to divide by zero, you will get an error) What
you should check is: IIF(denominator_ +denominator_n=0,"", [Do your
calculations])

Hi I have a formula that I am trying to remove any potential #Errors in
Access. I have looked at other postings online but can not find the
correct syntax to make my formula work.

I am basically trying to do:

IIF(ISERROR(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])),"
",
(([numerator1]+[numerator2]+[numerator3])/([deniminator1]+[deniminator2])))

I am having syntax problems with my parenthethis. This formula is an
example as I actually have 10 numerators and 5 denominators. Please
help. I can not just do an IIF statement =0 as this will only handles
0s and not errors. I need a nested ISERROR in an IIF.

Thanks,
Chris

 

[John Vinson]

The numerators have not been individually evaluated for null values.
Most will have numbers but a few are nulls. That is why I am setting
up the ISERRO formula here. Add/Divide them if normal, ignore if
because there is a 0 in one of the component numbers causing an error,
then the iserror kicks in.

NULL won't actually trigger an Error condition - any arithmetic
expression involving a NULL returns NULL (not #Error).

John W. Vinson[MVP]