• Status: Solved
• Priority: Medium
• Security: Public
• Views: 152

# Stuck on syntax

What is wrong with this code?  All I'm trying to do is replace the field with a "0" if the calculated value is less than "0".

=IIf(([txtBalToMake]/[txtPatterns_On])<0,0,=Nz([txtBalToMake]/[txtPatterns_On],1))
0
SteveL13
1 Solution

Commented:
``````=IIf(([txtBalToMake]/[txtPatterns_On])<0,0,Nz([txtBalToMake]/[txtPatterns_On],1))
``````
0

Commented:
Or this to avoid errors with nulls:

``````=IIf( NZ([txtBalToMake,0)/NZ(txtPatterns_On,1)<0,0, NZ([txtBalToMake,0)/NZ(txtPatterns_On,1))
``````
0

Commented:
One step further to avoid division by zero if txtPatterns_On is zero:

``````=IIf( NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) ) <0,0, NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) )
``````
0

Commented:
A little slow this morning Miriam?

;-)

Dale

PS - What time zone are you in?  You always seem to get the jump on me, I'm on US Eastern
0

Commented:
Missed a closing paren :-)

``````IIf( NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) ) <0,0, NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) ) )
``````

Dale,

I'm on the East Coast too, but wake up insanely early.
0

Author Commented:
I get a "Enter parameter value"
0

Commented:
What parameter is it asking for?

Double-check the spelling of the field name...

What I posted at http:#a38778340 is tested, and should work for you assuming that the textbox names are correct.
0

Commented:
You might also want to check the fieldnames in the recordsource of your form or report and also the sorting/grouping if you are working with a report.

(Its possible that something other than this control source is causing that prompt.)
0

Commented:
You might want to wrap all of the field names in brackets, but that should not be necessary.  I noticed that one of the earlier responses had a missing "]", but I believe mbizup dropped the brackets out of the latest version.

Also, if this is the control source for an unbound textbox, then I would probably go one step beyond  mbizup' last recommendation with:

=iif(NZ(txtPatterns_On,0) = 0, "Invalid Divisor", NZ(txtBalToMake,0)/txtPatterns_On)

This takes the NULL or Zero divisor totally out of the equation.
0

Commented:
are you expecting to get a negative result ? cause - (negative values) are the only ones less than 0  (zero)

try

=IIf(([txtBalToMake]/[txtPatterns_On]) = null,0,Nz([txtBalToMake]/[txtPatterns_On],1))
0

Commented:
``````= IIf( NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) ) <0,0, NZ(txtBalToMake,0)/iif(NZ(txtPatterns_On,1) = 0, 1,NZ(txtPatterns_On,1) ) )
``````
0

Commented:
I don't understand how converting a NULL or zero denominator  to a 1 solves anything.  It appears to me that it would be very misleading to your users.  Did you try:

=iif(NZ(txtPatterns_On,0) = 0, "Invalid Divisor", NZ(txtBalToMake,0)/txtPatterns_On)

This makes it very clear that the divisor is not valid for the computation, rather than assuming that it should be 1.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.