Between...And operator

[Irene SGA]

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?

 

[Jeff Boyce]

Irene

You may need to be more explicit with your "between". You could use ">="
(lower) and "<=" (upper) instead of Between... And...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Irene SGA]

Thanks Jeff,

I tried:

pass or fail: IIf([Average Actual Value]<=[USL] And [Average Actual
Value]>=[LSL],"Pass","Fail")

where,
USL=[Nominal]+[+Upper Tolerance]
LSL=[Nominal]+[-Lower Tolerance]

I get the same error. Note that it doesn't happen every time, just sometimes
but they don't make any sense.

Regards,
Irene

Irene

You may need to be more explicit with your "between". You could use ">="
(lower) and "<=" (upper) instead of Between... And...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem
comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?

 

[Jeff Boyce]

My next guess would be that there are "missing values" (perhaps "nulls") for
some of the components.

Do you wish to exclude any records with Null values in any of the
components, or do you want to convert those null values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,

I tried:

pass or fail: IIf([Average Actual Value]<=[USL] And [Average Actual
Value]>=[LSL],"Pass","Fail")

where,
USL=[Nominal]+[+Upper Tolerance]
LSL=[Nominal]+[-Lower Tolerance]

I get the same error. Note that it doesn't happen every time, just
sometimes
but they don't make any sense.

Regards,
Irene

Irene

You may need to be more explicit with your "between". You could use ">="
(lower) and "<=" (upper) instead of Between... And...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem
comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?

 

[David F Cox]

Is it possible that it only appears to fail, that the result displays with
less precision that the value actually has?

 

[Irene SGA]

David,

Yes I think that's the problem. I noticed that when I add zero to any
control, the result is not the control itself, but a different number. Say I
add 0.36+0.0, the result should be 0.36, but it is 0.3599999 or some crazy
number like this, so that's making my data fail. What can I do?

Thanks

Is it possible that it only appears to fail, that the result displays with
less precision that the value actually has?

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem
comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?

 

[Lynn Trapp]

Irene,
Your problem is related to the nature of Double precision numbers. If you
are able to work with a maximum of 4 decimal places, you can change your
datatype to Currency and the problem should go away.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com

David,

Yes I think that's the problem. I noticed that when I add zero to any
control, the result is not the control itself, but a different number. Say
I
add 0.36+0.0, the result should be 0.36, but it is 0.3599999 or some crazy
number like this, so that's making my data fail. What can I do?

Thanks

Is it possible that it only appears to fail, that the result displays
with
less precision that the value actually has?

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem
comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?

 

[Jeff Boyce]

Irene

From the "symptom", I suspect the data type for this field is Single or
Double. These are, I believe, stored as binary values, so there's rounding
issues.

If you need no more than four decimal places, use the Currency datatype
instead -- I don't believe this is stored as binary, so .36 + 0 = .36

Regards

Jeff Boyce
Microsoft Office/Access MVP

David,

Yes I think that's the problem. I noticed that when I add zero to any
control, the result is not the control itself, but a different number. Say
I
add 0.36+0.0, the result should be 0.36, but it is 0.3599999 or some crazy
number like this, so that's making my data fail. What can I do?

Thanks

Is it possible that it only appears to fail, that the result displays
with
less precision that the value actually has?

I've been using the Between...And operator for a calculated field:

pass or fail: IIf([Average Actual Value] Between ([Nominal]+[-Lower
Tolerance]) And ([Nominal]+[+Upper Tolerance]),"Pass","Fail")

The Average Actual Value (AAV) is an average of 4 values. The problem
comes
when the AAV= either the upper or lower limit. I get inconsistencies,
sometimes it passes and sometimes it fails.
any ideas on what I'm doing wrong?