NZ is sometimes a Negative?

[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)

 

[Ken Snell [MVP]]

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)

 

[AlCamp]

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

 

[Dave Elliott]

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)

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)

 

[Ken Snell [MVP]]

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>

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)

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)

 

[John Vinson]

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

How about

Iif(Nz([Bid]) = 0, 0, <expression>)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dave Elliott]

THANKS, that did the trick.

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>

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)

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)