checkbox update problem

[anil]

hi all
I have 2 tables
tblresult(ResultID,ParameterID,ResultValue,ResultISPass) and
tblParameter(parameterID,ParameterName,P_HIghLimit,P_LowLimit)

Now when i enter the resultvalue in tblResult or frmResult I want that
ResultISPass(checkBox) should be automatically updated.
conditions are

(1) Check for P_highlimit and P_LowLimit - if null then ResultISPass
=Null(3rd state of checkbox)

(2) If Resultvalue<=P_highlimit and ResultValue>=P_LowLimit then
ResultISPass = true
(3) else ResultIsPass = False

I am bit confused that I should use it in query which is not working
fine as

UPDATE tblParameter INNER JOIN tblResults ON (tblParameter.ParameterID
= tblResults.ParameterID)
SET tblResults.ResultIsPass = True
WHERE (((tblResults.ResultValue)<=nz([tblparameter].[P_highlimit]) And
(tblResults.ResultValue)>=nz([tblparameter].[P_LowLimit])));

This also does not consider third state of checkbox.

If I do it in code on update of ResultValue in form as

Private Sub ResultValue_AfterUpdate()

' if P_HighLimit or P_lowLimit = Null then
Me.ResultIsPass = Null
else If (Me.ResultValue.Value <=
Forms!frmparameter!P_highlimit.Value) Then
Me.ResultIsPass = True
Elseif (Me.ResultValue.Value >=
Forms!frmparameter!P_Lowlimit.Value) Then
Me.ResultIsPass = False

End If
End Sub

Here it is giving error in Forms!frmparameter!P_highlimit.Value.
Please help me out
thanks
anil

 

[John Spencer]

I would try the following

UPDATE tblParameter INNER JOIN tblResults
ON (tblParameter.ParameterID= tblResults.ParameterID)
SET tblResults.ResultIsPass =
IIF(tblparameter].[P_highlimit] Is Null Or tblResults.ResultValue is Null,
Null, tblResults.Resultvalue<=tblparameter.P_highlimit and
tblResults.ResultValue>=tblparameter.P_LowLimit )

True
WHERE (((tblResults.ResultValue)<=nz([tblparameter].[P_highlimit]) And
(tblResults.ResultValue)>=nz([tblparameter].[P_LowLimit])));

 

[anil]

thanks for ur reply john
But sorry I did not explain properly last time.I missed some points
case (1)
if P_HighLimit is Null and P_lowLimit is '3' (integer) then
resultValue should compare with P_LowLimit only as
if ResultValue>=P_lowlimit then
ResultIsPass =true else false
Case (2)
if P_HighLimit is integer and P_lowLimit is Null then resultValue
should compare with P_HighLimit only as
if ResultValue<=P_Highlimit then
ResultIsPass=true else false
Case (3)
if Both P_highLimit and P_lowLimit are Null then
ResultValue do not compare and
ResultIsPass =NULL
Case(4) If P_HighLimit is integer and P_Lowlimit is integer then
ResultValue>=P_Lowlimit and ResultValue<=P_highLimit
ResultIsPass =true else False

I hope it will clarify the problem
I tried using vba code but still not successful because in recordset it
does not update and calling as function in query does give error..
I will post my logic

If IsNull(Maxlimit) Then
If ResultValue <= Maxlimit Then
Rslt = True
Else
Rslt = False
If IsNull(MinLimit) Then
If ResultValue >= MinLimit Then
Rslt = True
Else
Rslt = False
End If
Else
If Not IsNull(MinLimit) Then
If ResultValue >= MinLimit Then
Rslt = True
Else
Rslt = False
End If
End If
End If

End If
Rslt = Null
End If

hope now U can help me what to do
thanks
anil

 

[John Spencer]

Perhaps the following will work for you.


UPDATE tblParameter INNER JOIN tblResults
ON (tblParameter.ParameterID= tblResults.ParameterID)
SET tblResults.ResultIsPass =
IIF(tblparameter].[P_highlimit] Is Null AND tblResults.ResultValue is Null,
Null,
tblResults.Resultvalue<=NZ(tblparameter.P_highlimit,TblResults.ResultValue) and
tblResults.ResultValue>=NZ(tblparameter.P_LowLimit,tblResults.ResultValue) )

 

[anil]

thanks for the reply
it is giving syntax error(missing Operator).
2 questions.
1- is it complete query or the idea because ResultValue is never null
and u did not consider min value equal to null.
may be I am confused so not able to get it.I am writing some example of
output I need.

PARAMATER- RESULTVALUE- HIGHLIMIT -LOWLIMIT-RESULTISPASS
A 0.28 0.2 "" FALSE
A 0.18 0.2 "" TRUE
B 8.5 8.5 6.5 TRUE
B 6.3 8.5 6.5 FALSE
B 8.6 8.5 6.5 FALSE
C 15 "" "" ""
D 0 "" 0 TRUE
D 2 "" 0 FALSE

I hope that will clarify my problem more better.If the query is right
then I will try to work or fix that.
thanks for the trouble u r taking
anil

 

[John Spencer]

Sorry, I got the first part of the statement wrong. I should have been
checking P_highlimit and P_lowLimit for null

Next there was a missing "[" in the IIF statement

UPDATE tblParameter INNER JOIN tblResults
ON (tblParameter.ParameterID= tblResults.ParameterID)
SET tblResults.ResultIsPass =
IIF([tblparameter].[P_highlimit] Is Null AND tblparameter.P_LowLimit is
Null, Null,
tblResults.Resultvalue<=NZ(tblparameter.P_highlimit,TblResults.ResultValue)
and
tblResults.ResultValue>=NZ(tblparameter.P_LowLimit,tblResults.ResultValue))

I did consider min value equal to null by using the nz function.

Stepping through the logic for your first item
The test is not true since HighLimit has a value, so go to the False
argument of the IIF Statement
Line 1
..28 <= .2 and .28>=.28 (the NZ changes the null value to .28) returns False
Line 2
..18<= .2 and .18>=.18(the NZ changes the null value to .18) returns True
Line 7
0<= 0 (the NZ changes the null value to 0) and 0>=0 returns True


You could change the NZ to return 9999 if you know that the maximum of the
high_Limit is less than that (or some other value) and change the Low_Limit
to 0 if you know that is the minimum low limit.

UPDATE tblParameter INNER JOIN tblResults
ON (tblParameter.ParameterID= tblResults.ParameterID)
SET tblResults.ResultIsPass =
IIF([tblparameter].[P_highlimit] Is Null AND tblparameter.P_LowLimit is
Null, Null,
tblResults.Resultvalue<=NZ(tblparameter.P_highlimit,9999) and
tblResults.ResultValue>=NZ(tblparameter.P_LowLimit,0))

 

[anil]

Thanks john
it works wonderful.Although I also wrote a function last night to get
it which is here for ur reference

Function rslt(Maxlimit As Variant, MinLimit As Variant, ResultValue As
Variant) As Variant
If IsNull(Maxlimit) And IsNull(MinLimit) Then
rslt = ""
ElseIf Not IsNull(Maxlimit) And IsNull(MinLimit) Then
rslt = IIf(ResultValue <= Maxlimit, "True", "False")
ElseIf IsNull(Maxlimit) And Not IsNull(MinLimit) Then
rslt = IIf(ResultValue >= MinLimit, "True", "False")
ElseIf Not IsNull(Maxlimit) And Not IsNull(MinLimit) Then
rslt = IIf(ResultValue <= Maxlimit And ResultValue >= MinLimit,
"True", "False")
End If
End Function

This also solved the purpose,but ur query helps to reduce one step
which is great.

I just have one small query or information required.We know that check
box has 3 states-yes,no,null.I can switch on null option in the form
which is default only when no entry is there.Is there any option to
state Null in tables as i want to clearly see the null value in results
or I should change it to text and use true,False and NULL.

thanks again for ur help
anil

 

[John Spencer]

If you want to store Null in a field, you cannot use the Yes/No (Boolean)
field type. Boolean fields are always True (-1 in Access) or False(0) in
Access.

You will have to use another field type. Text works if you want to store a
string value or a null.

You could also use a number field (Integer). That can store 0, -1 and null.
Which you can then use with a checkbox control to show the three states of
Null (gray), Checked, or Unchecked.

 

[anil]

thanks john for ur help.

If you want to store Null in a field, you cannot use the Yes/No (Boolean)
field type. Boolean fields are always True (-1 in Access) or False(0) in
Access.

You will have to use another field type. Text works if you want to store a
string value or a null.

You could also use a number field (Integer). That can store 0, -1 and null.
Which you can then use with a checkbox control to show the three states of
Null (gray), Checked, or Unchecked.

 

[anil]

hi john
there is one more problem.using ur query i am able to get results.in
that case it was only for water.now i have added waste water.
problem is -
Normally for WATER one parameter has one lowlimit and one high limit
(e.g ph has low limit of 6.5 and high limit of 8.5) and result must be
between max and min limits to be pass .Parameter may be only high
limit(e.g Calcium has high limit of 22 and no low limit) and value is
compared with high limit or may be no limits .That We have done
BUT for WASTEWATER one parameter can have many high limits (only high
no low limits) depending upon sites.e.g
Parameter site highlimit
ph ara1 9
ph ara2 8
ph ara3 9
ph beu1 9
ph cha 8.
because depending upon water treatment plant and the process undergoing
,there can be different values.
Tables are -
tblResult(ResultID,SampIeID,ParameterID,ResultValue,ResultIsPass(yes/No))
tblSite(SiteID,SiteCode) , tblParameter(ParameterID,ParameterName) ,
tblSample(SampleID,ParameterID,SampleTakenFor)
tblParamLimits(LimitsID,ParameterID,SiteID,(Water)HighLimit,(Water)LowLimit,(WasteWater)SiteLimit)

please can u help me to fix relationships by changing table fields as
to get final result:
ResultID-SampleID-ParameterID-SampleTakenFOR-ResultValue-ResultISPAss
1 1 PH Water
9 no (b/n 8.5 and 6.5)
2 2 ph WasteWater 8
Yes(<=9)
3 3 PH WASTEWATER 10
NO(<=9)

Actually I want that if SampleTAkenFor is WATER then it should compare
for WATER parameters as in above case 8.5 and 6.5
whereas in case of WASTEWATER it should compare with 9.
hope u understand
thanks
ANIL