IIf statement ignoring negative value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

[MINPEN]=.005
[MAXPEN]=0.020
[p1]=.075
[t1]=.060

=IIf([p1] Is Null,Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))

I tested [t1]-[p1] and received -.015. The IIf statement is returning, PASS
when it should be FAIL. Why?
 
It looks to me like your IIF() statement is comparing the difference to some
other values (i.e., [MINPEN], [MAXPEN]). One possibility is that these
comparison values are the cause of the "PASS".

Another possibility is that there is a Null in one/other of these comparison
values. In Access, "nulls propogate" -- that is, a null in a calculation
results in a null.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I tested the values [MINPEN], [MAXPEN], [t1] and [p1] and they yield the
correct values.

Jeff Boyce said:
It looks to me like your IIF() statement is comparing the difference to some
other values (i.e., [MINPEN], [MAXPEN]). One possibility is that these
comparison values are the cause of the "PASS".

Another possibility is that there is a Null in one/other of these comparison
values. In Access, "nulls propogate" -- that is, a null in a calculation
results in a null.

Regards

Jeff Boyce
Microsoft Office/Access MVP


zachnjoe said:
[MINPEN]=.005
[MAXPEN]=0.020
[p1]=.075
[t1]=.060

=IIf([p1] Is Null,Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))

I tested [t1]-[p1] and received -.015. The IIf statement is returning,
PASS
when it should be FAIL. Why?
Click to expand...
Click to expand...
 
Try

=IIf(IsNull([p1]),Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))
 
Looks good, but maybe the issue is related to the "And".

Have you tried just a single comparison (i.e., greater than or equal to
MIN)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


zachnjoe said:
I tested the values [MINPEN], [MAXPEN], [t1] and [p1] and they yield the
correct values.

Jeff Boyce said:
It looks to me like your IIF() statement is comparing the difference to
some
other values (i.e., [MINPEN], [MAXPEN]). One possibility is that these
comparison values are the cause of the "PASS".

Another possibility is that there is a Null in one/other of these
comparison
values. In Access, "nulls propogate" -- that is, a null in a calculation
results in a null.

Regards

Jeff Boyce
Microsoft Office/Access MVP


zachnjoe said:
[MINPEN]=.005
[MAXPEN]=0.020
[p1]=.075
[t1]=.060

=IIf([p1] Is Null,Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))

I tested [t1]-[p1] and received -.015. The IIf statement is returning,
PASS
when it should be FAIL. Why?
Click to expand...
Click to expand...
Click to expand...
 
The following seems to work:

Debug.Print IIf(IsNull(.075) = True, Null, IIf((.060 - .075) >= .005 AND
(.060 - .075) <= 0.020,"PASS","FAIL"))

Possibly bracketing is needed.

I used IsNull = True where you just used Is Null.

You are mixing P1 and p1.
 
I tried it and got the same result.

Douglas J Steele said:
Try

=IIf(IsNull([p1]),Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


zachnjoe said:
[MINPEN]=.005
[MAXPEN]=0.020
[p1]=.075
[t1]=.060

=IIf([p1] Is Null,Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))

I tested [t1]-[p1] and received -.015. The IIf statement is returning, PASS
when it should be FAIL. Why?
Click to expand...
Click to expand...
 
This works.

=IIf(IsNull([p1]),Null,IIf([t1]-[P1]>=[MINPEN] And
[t1]-[P1]<=[MAXPEN],"PASS","FAIL"))
 
Back
Top