dividing creates an #Error... (1 Viewer)

[deejay_totoro]

Hello,

I am trying to do a simple division. But I am getting an #Error when Access query tries to divide 0 by 0. Here is my example:

fldOne = 0
fldTwo = 0

fldCalc: IIf(([fldOne]/[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = #Error

However:

fldCalc: IIf(([fldOne]+[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = 0

If I change the IIF to [0]+[0] (plus) then it works. So I think I am trying to find out how to divide two fields that both contain zeros? (...I think!)

Any help appreciated!

dj_T

 

[boblarson]

You can't divide by zero (0) so you have to solve for that BEFORE dividing:

=IIf([fldTwo]=0,0,[fldOne]/[fldTwo])

 

[jazi]

Still wondering...

You can't divide by zero (0) so you have to solve for that BEFORE dividing:

=IIf([fldTwo]=0,0,[fldOne]/[fldTwo])

I put this equation in a new field, but now I have many fields with a ###### for all the fields that do not have zeros, and all the zero fields are just fine.

What am I doing wrong?

 

[boblarson]

If it is showing ###### then your text box isn't wide enough to display the digits.

 

[gemma-the-husky]

=IIf([fldTwo]=0,0,[fldOne]/[fldTwo])

yuo STILL shouldnt be able to use this. an iif evaluates BOTH parts of the statement, and therefore will still fail if fldtwo = 0

you either need to do

EITHER

if fldtwo = 0 then
    answer=0 
else
    answer = fld1/fld2
end if


OR TRAP THE ERROR

on error goto fail
answer = fld1/fld2

exithere:
   exit sub

fail:
{assuming the error is a dvide by zero - you may want to test the error   number
     answer = 0
     resume exithere

 

[boblarson]

=IIf([fldTwo]=0,0,[fldOne]/[fldTwo])

yuo STILL shouldnt be able to use this. an iif evaluates BOTH parts of the statement, and therefore will still fail if fldtwo = 0

you either need to do

EITHER

if fldtwo = 0 then
    answer=0 
else
    answer = fld1/fld2
end if


OR TRAP THE ERROR

on error goto fail
answer = fld1/fld2

exithere:
   exit sub

fail:
{assuming the error is a dvide by zero - you may want to test the error   number
     answer = 0
     resume exithere

That is strange because I've used it that way many times before and it dealt with zeros just fine. It wouldn't deal with NULLS but it did deal with zeros.

 

[boblarson]

Okay, figured it out. You CAN use

=IIf([fldTwo]=0,0,[fldOne]/[fldTwo])

IF - you refer to the CONTROLS and NOT the fields. So, the controls should be named differently than the fields and, if they were named txtfldTwo and txtfldOne it would be:

=IIf([txtfldTwo]=0,0,[txtfldOne]/[txtfldTwo])

and that DOES work. I KNEW I'd done that before.

 

[jazi]

Grr, I was going too fast, and I forgot, sorry for such a crappy Question!

What if you want to add more of an equation to it, such as:

=IIf([Grant Shipped]=0,0,(Sum[Grant Rejected]/Sum[Grant Shipped])*1000000)

But I get a syntax error...any clues?

 

[gemma-the-husky]

Strange, Bob

this is part of the help for iif function, A97

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

 

[boblarson]

I know what it says, but the fact is that if you use the field names it does error out but if you use the control names it doesn't. Strange, but true...

 

[ChrisO]

It was my understanding the IIf function behaved differently between Jet and VBA.

This question was asked about queries and the function in queries does not fail.
In VBA both sections are evaluated because all arguments passed to all functions, not just the IIF, are evaluated before they are passed.

But Bob, if you’ve got this to work OK can you post a small demo please?

Regards,
Chris.

 

[boblarson]

Here's a sample with the IIF as the controlsource of the text box. Scroll through the records and that it works with the 2nd field being 0, as well as not.

 

[boblarson]

Oh, and I was talking about controlsources, which probably explains it because it is not in VBA.

 

[ChrisO]

Thanks Bob.

Yep that’s about it…a control source is not VBA and will behave exactly like Jet in a query.

It seems a pity that MS wrote those comments about the IIf function (the divide by zero stuff) because it almost masks the fact that it occurs with all VBA functions/subroutines.

Regards,
Chris.