IIf statement ignoring negative value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zachnjoe - 23 Jun 2006 14:31 GMT

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

Reply to this Message

Jeff Boyce - 23 Jun 2006 15:13 GMT

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

> [MINPEN]=.005
> [MAXPEN]=0.020
[quoted text clipped - 7 lines]
> PASS
> when it should be FAIL. Why?

Reply to this Message

zachnjoe - 23 Jun 2006 15:38 GMT

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

> 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
[quoted text clipped - 20 lines]
> > PASS
> > when it should be FAIL. Why?

Reply to this Message

Jeff Boyce - 23 Jun 2006 16:07 GMT

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

>I tested the values [MINPEN], [MAXPEN], [t1] and [p1] and they yield the
> correct values.
[quoted text clipped - 25 lines]
>> > PASS
>> > when it should be FAIL. Why?

Reply to this Message

Douglas J Steele - 23 Jun 2006 16:01 GMT

Try

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> [MINPEN]=.005
> [MAXPEN]=0.020
[quoted text clipped - 6 lines]
> I tested [t1]-[p1] and received -.015. The IIf statement is returning, PASS
> when it should be FAIL. Why?

Reply to this Message

zachnjoe - 23 Jun 2006 16:18 GMT

I tried it and got the same result.

> Try
>
[quoted text clipped - 12 lines]
> PASS
> > when it should be FAIL. Why?

Reply to this Message

Jerry Whittle - 23 Jun 2006 16:14 GMT

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.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> [MINPEN]=.005
> [MAXPEN]=0.020
[quoted text clipped - 6 lines]
> I tested [t1]-[p1] and received -.015. The IIf statement is returning, PASS
> when it should be FAIL. Why?

Reply to this Message

Klatuu - 23 Jun 2006 16:43 GMT

This works.

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

> [MINPEN]=.005
> [MAXPEN]=0.020
[quoted text clipped - 6 lines]
> I tested [t1]-[p1] and received -.015. The IIf statement is returning, PASS
> when it should be FAIL. Why?

Reply to this Message