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?

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" <Irene (E-Mail Removed)> wrote in message
news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
> 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?

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

"Jeff Boyce" wrote:

> 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" <Irene (E-Mail Removed)> wrote in message
> news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
> > 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?
>
>
>

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

"Irene SGA" <(E-Mail Removed)> wrote in message
news:58606595-5703-4D78-944B-(E-Mail Removed)...
> 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
>
> "Jeff Boyce" wrote:
>
>> 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" <Irene (E-Mail Removed)> wrote in message
>> news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
>> > 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?
>>
>>
>>

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


"Irene SGA" <Irene (E-Mail Removed)> wrote in message
news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
> 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,

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

"David F Cox" wrote:

> Is it possible that it only appears to fail, that the result displays with
> less precision that the value actually has?
>
>
> "Irene SGA" <Irene (E-Mail Removed)> wrote in message
> news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
> > 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?
> >
>
>
>

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


"Irene SGA" <(E-Mail Removed)> wrote in message
news:687A6976-6FC9-425F-B8F5-(E-Mail Removed)...
> 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
>
> "David F Cox" wrote:
>
>> Is it possible that it only appears to fail, that the result displays
>> with
>> less precision that the value actually has?
>>
>>
>> "Irene SGA" <Irene (E-Mail Removed)> wrote in message
>> news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
>> > 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?
>> >
>>
>>
>>

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

"Irene SGA" <(E-Mail Removed)> wrote in message
news:687A6976-6FC9-425F-B8F5-(E-Mail Removed)...
> 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
>
> "David F Cox" wrote:
>
>> Is it possible that it only appears to fail, that the result displays
>> with
>> less precision that the value actually has?
>>
>>
>> "Irene SGA" <Irene (E-Mail Removed)> wrote in message
>> news:79EBAFD2-ECF4-4BFB-9CE7-(E-Mail Removed)...
>> > 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?
>> >
>>
>>
>>