NZ is sometimes a Negative?

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

If there is no criteria for Bid, then it causes an error #Div/0!
Bid's Default is 0
How can I get rid of this?

=NZ([NetAfterCom]/[Bid],0)
 
The expression that you posted does not do what you think.... the Nz
function is being applied to the result of the division of NetAfterCom by
Bid.

If you want Nz to apply to Bid, you'd use this:
=[NetAfterCom]/Nz([Bid],0)

But then, of course, if Bid is Null, this expression would make Bid equal to
0, which again will give you the division by 0 error.

So, you'll need to choose a different default value, such as 1:
=[NetAfterCom]/Nz([Bid],1)
 
Dave,
I'd go with...
IIF(IsNull([Bid]), 0, [NetAfterCom]/[Bid])
This avoids the calculation altogether if Bid IsNull.
hth
Al Camp
 
This is my code for a textbox named Lab. It gets criteria from Labor and
Bid
Bid is normally 0 until a amount is inputted
If Bid isNull or 0, then I need the code to not error out.
Control source is set to the below for textbox Lab
Tried
IIF(IsNull([Bid]), 0, [Labor]/[Bid])
but with no luck.


=NZ([Labor]/[Bid],0)


AlCamp said:
Dave,
I'd go with...
IIF(IsNull([Bid]), 0, [NetAfterCom]/[Bid])
This avoids the calculation altogether if Bid IsNull.
hth
Al Camp

Dave Elliott said:
If there is no criteria for Bid, then it causes an error #Div/0!
Bid's Default is 0
How can I get rid of this?

=NZ([NetAfterCom]/[Bid],0)
Click to expand...
Click to expand...
 
Because the IIf is in the control source of a textbox, ACCESS will try to
interpret both the True and False arguments, so you must test the Bid value
as part of the denominator, not as part of the entire expression. And you
must decide what you want Bid to be if it's zero or null.

So, if you don't want to try the expression that I posted earlier:
=[Labor]/Nz([Bid], 1)

then try this:
=[Labor]/IIf(IsNull([Bid])=True Or [Bid]=0,1,[Bid])

--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
This is my code for a textbox named Lab. It gets criteria from Labor and
Bid
Bid is normally 0 until a amount is inputted
If Bid isNull or 0, then I need the code to not error out.
Control source is set to the below for textbox Lab
Tried
IIF(IsNull([Bid]), 0, [Labor]/[Bid])
but with no luck.


=NZ([Labor]/[Bid],0)


AlCamp said:
Dave,
I'd go with...
IIF(IsNull([Bid]), 0, [NetAfterCom]/[Bid])
This avoids the calculation altogether if Bid IsNull.
hth
Al Camp

Dave Elliott said:
If there is no criteria for Bid, then it causes an error #Div/0!
Bid's Default is 0
How can I get rid of this?

=NZ([NetAfterCom]/[Bid],0)
Click to expand...
Click to expand...
Click to expand...
 
THANKS, that did the trick.


Ken Snell said:
Because the IIf is in the control source of a textbox, ACCESS will try to
interpret both the True and False arguments, so you must test the Bid
value
as part of the denominator, not as part of the entire expression. And you
must decide what you want Bid to be if it's zero or null.

So, if you don't want to try the expression that I posted earlier:
=[Labor]/Nz([Bid], 1)

then try this:
=[Labor]/IIf(IsNull([Bid])=True Or [Bid]=0,1,[Bid])

--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
This is my code for a textbox named Lab. It gets criteria from Labor and
Bid
Bid is normally 0 until a amount is inputted
If Bid isNull or 0, then I need the code to not error out.
Control source is set to the below for textbox Lab
Tried
IIF(IsNull([Bid]), 0, [Labor]/[Bid])
but with no luck.


=NZ([Labor]/[Bid],0)


AlCamp said:
Dave,
I'd go with...
IIF(IsNull([Bid]), 0, [NetAfterCom]/[Bid])
This avoids the calculation altogether if Bid IsNull.
hth
Al Camp

If there is no criteria for Bid, then it causes an error #Div/0!
Bid's Default is 0
How can I get rid of this?

=NZ([NetAfterCom]/[Bid],0)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top