Need Help Checking Values in one table against two min max values in another

[kevinjouco]

Hello

Have searched the group for a solution to the following problem without
success:

Table 1 has Ref No (No Duplicates) & Min Max Value Fields
ie Ref No 1 Min 1 Max 10
Ref No 2 Min 11 Max 20 etc

Table 2 is linked by Ref No (Allows Duplicates) & Recorded Value
Ie 1) Ref No 1 Recorded Value 5
2)Ref No 1 Recorded Value 8
3)Ref No 2 Recorded Value 19
4)Ref No 2 Recorded Value 7 etc

The idea is the Recorded Value is accepted for 1,2,& 3 however an
invalid entry pop up disallows entry of 4 as it is outside the Value.
Unfortunately, what happens in this case is that 3 throws up the error
as the code used always compares against the first min max Values
recorded, which means 4 is accepted

The Code Used is as follows:

Private Sub Nom_Burst_Pressure_BeforeUpdate()
Dim varUP, varLow, varAct As Variant

varUP = DLookup("[Max]", "Table 1", "")

varLow = DLookup("[Min]", "Table 1", "")

varAct = Me.Recorded_Value
If varAct > varUP Then
MsgBox "This number is Above maximum value"
DoCmd.CancelEvent
ElseIf varAct < varLow Then
MsgBox "This number is Below minimum value"
DoCmd.CancelEvent

End If

End Sub

How do I compare Recorded Value in table 2 for each Ref No against min
max values in table 2 where the Ref No is the same

Please Help

[Ronald Roberts]

ke********@hotmail.com wrote:

Hello

Have searched the group for a solution to the following problem without
success:

Table 1 has Ref No (No Duplicates) & Min Max Value Fields
ie Ref No 1 Min 1 Max 10
Ref No 2 Min 11 Max 20 etc

Table 2 is linked by Ref No (Allows Duplicates) & Recorded Value
Ie 1) Ref No 1 Recorded Value 5
2)Ref No 1 Recorded Value 8
3)Ref No 2 Recorded Value 19
4)Ref No 2 Recorded Value 7 etc

The idea is the Recorded Value is accepted for 1,2,& 3 however an
invalid entry pop up disallows entry of 4 as it is outside the Value.
Unfortunately, what happens in this case is that 3 throws up the error
as the code used always compares against the first min max Values
recorded, which means 4 is accepted

The Code Used is as follows:

Private Sub Nom_Burst_Pressure_BeforeUpdate()
Dim varUP, varLow, varAct As Variant

varUP = DLookup("[Max]", "Table 1", "")

varLow = DLookup("[Min]", "Table 1", "")

varAct = Me.Recorded_Value
If varAct > varUP Then
MsgBox "This number is Above maximum value"
DoCmd.CancelEvent
ElseIf varAct < varLow Then
MsgBox "This number is Below minimum value"
DoCmd.CancelEvent

End If

End Sub

How do I compare Recorded Value in table 2 for each Ref No against min
max values in table 2 where the Ref No is the same

Please Help

This is air code.
The statement "varAct = Me.Recorded_Value", I think is incorrect.
Me.Recorded_Value is what your are testing and RefNo is what will be
returned if Recorded_Value is valid.

If isNull(Dlookup("RefNo","Table 1",Me.RecordedValue & "=> Min and " & _
RecordedValue & " =< Max") Then
msgbox "Invalid Entry", 48
Else
varAct=Dlookup("RefNo","Table 1",Me.RecordedValue & "=> Min and " & _
RecordedValue & " =< Max")
Endif

Ron

[kevinjouco]

Thanks for quick reply and sorry about air code, unfortunately I am
relatively new to this. I don't quite understand what you are saying
where:
Me.Recorded Value is what i am testing.

Note:
Ref No will be duplicated and the Recorded Values vary in table 2
However once Ref No and Min Max Values are set in table 1 they remain
the same no duplicate ref nos or changes to the min max Values.
The problem is I have a moving target ie Ref No 1 from Table 1 can only
be hit by Ref No 1 from Table 1 with the Recorded Values measured
against the Min Max Values and Ref No 2 from Table 1 can only be hit by
Ref No 2 from Table 2 with the Recorded Values measured against the Min
Max Values and so on.

Unfortunately when I tried the code suggested as follows it failed to
get past the first line due to a Run time error 3035 and I remain
unsuccessful Please Help:

Private Sub P_Value_BeforeUpdate(Cancel As Integer)

Dim varAct As Variant

If IsNull(DLookup("A", "A1", Me.P_Value & "=> Min" & Me.P_Value & " =<
Max")) Then

MsgBox "Invalid Entry", 48
Else
varAct = DLookup("A", "A1", Me.P_Value & "=> Min and " & _
P_Value & " =< Max")
End If

End Sub

[Salad]

ke********@hotmail.com wrote:

Hello

Have searched the group for a solution to the following problem without
success:

Table 1 has Ref No (No Duplicates) & Min Max Value Fields
ie Ref No 1 Min 1 Max 10
Ref No 2 Min 11 Max 20 etc

Table 2 is linked by Ref No (Allows Duplicates) & Recorded Value
Ie 1) Ref No 1 Recorded Value 5
2)Ref No 1 Recorded Value 8
3)Ref No 2 Recorded Value 19
4)Ref No 2 Recorded Value 7 etc

The idea is the Recorded Value is accepted for 1,2,& 3 however an
invalid entry pop up disallows entry of 4 as it is outside the Value.
Unfortunately, what happens in this case is that 3 throws up the error
as the code used always compares against the first min max Values
recorded, which means 4 is accepted

The Code Used is as follows:

Private Sub Nom_Burst_Pressure_BeforeUpdate()
Dim varUP, varLow, varAct As Variant

varUP = DLookup("[Max]", "Table 1", "")

varLow = DLookup("[Min]", "Table 1", "")

varAct = Me.Recorded_Value
If varAct > varUP Then
MsgBox "This number is Above maximum value"
DoCmd.CancelEvent
ElseIf varAct < varLow Then
MsgBox "This number is Below minimum value"
DoCmd.CancelEvent

End If

End Sub

How do I compare Recorded Value in table 2 for each Ref No against min
max values in table 2 where the Ref No is the same

Please Help

I'm not so crazy about using words to define a field like Min or Max
that are really function mames. But that's your problem in deciding
whether or not to keep/use. Getting onto your problem

'first, get the min/max for the specific ref no
varUP = NZ(DLookup("[Max]", "Table 1", "[Ref No] = " & Me![Ref No]),0)
varLow = NZ(DLookup("[Min]", "Table 1", "[Ref No] = " & Me![Ref No]),0)

'set to zero in case an up/low/act is null
varAct = NZ(Me.Recorded_Value,0)
If varAct > varUP Then
MsgBox "This number is Above maximum value"
Cancel = True
Elseif varAct < varLow Then
MsgBox "This number is Below minimum value"
Cancel = True
End IF

[Bob Quintal]

ke********@hotmail.com wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

Hello

Have searched the group for a solution to the following
problem without success:

The Code Used is as follows:

Private Sub Nom_Burst_Pressure_BeforeUpdate()
Dim varUP, varLow, varAct As Variant

varUP = DLookup("[Max]", "Table 1", "")

varLow = DLookup("[Min]", "Table 1", "")

varAct = Me.Recorded_Value
If varAct > varUP Then
MsgBox "This number is Above maximum value"
DoCmd.CancelEvent
ElseIf varAct < varLow Then
MsgBox "This number is Below minimum value"
DoCmd.CancelEvent

End If

End Sub

How do I compare Recorded Value in table 2 for each Ref No
against min max values in table 2 where the Ref No is the same

Please Help

First let me caution you about field names that are the same as
reserved words in Access Basic, like min, max, name.....
Avoid them and avoid spaces in field names too, they can mess
you up.

Your DLookup calls are incorrect.
DLookup("[Max]", "Table 1", "") will return the first record in
your table, because you have the empty string as criteria. What
you need is to put a criterium in hteere which selects the
correct pair of limits.

DLookup("[Max]", "Table 1", "[Ref No] = " & me.[ref no]) will
give you the correct limit


--
Bob Quintal

PA is y I've altered my email address.

[kevinjouco]

Thanks for your replies, especially Salad that code works perfectly.