Reply to thread

Message

[QUOTE="Steve Sanford, post: 2099101"]
It sounds like there might be 40 to 48 conditions. That's going to get ugly
quick.

With that many conditions it might be easier and cleaner to use a table and
code to look up get the result for the third text box.

The table, lets call it "tblRules", could have 6 fields:

name                           type
lngRules_PK                 AutoNumber
intFirstBegin                   Integer
intFirstEnd                     Integer
intSecondBegin               Integer
intSecondEnd                 Integer
intThird                         Integer

where "First", "Second" & "Third" refer to the text boxes.

The first and second text boxes have code in the after update event. The
code would look something like this:  (watch for line wrap!)

'----------------------------------------------

Private Sub tbFirst_AfterUpdate()
GetThirdValue
End Sub

Private Sub tbSecond_AfterUpdate()
GetThirdValue
End Sub

Sub GetThirdValue()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim strSQL As String
Dim varT1 As Integer
Dim varT2 As Integer

varT1 = Nz(Me.tbFirst, 0)
varT2 = Nz(Me.tbSecond, 0)

If Not IsNull(varT1) And Not IsNull(varT2) Then
Set d = CurrentDb

strSQL = "SELECT intThird FROM tblRules"
strSQL = strSQL & " WHERE (intFirstBegin <= " & varT1 & " AND
intFirstEnd >= " & varT1 & ")"
strSQL = strSQL & " And (intSecondBegin <= " & varT2 & " AND
intSecondEnd >= " & varT2 & ");"
'        Debug.Print strSQL
Set r = d.OpenRecordset(strSQL)

' might need more limits if recordset returns
' more than one record
If Not r.BOF And Not r.EOF Then
Me.tbThird = r.Fields("intThird")
Else
Me.tbThird = Null
End If

r.Close
Set r = Nothing
Set d = Nothing

End If
End Sub
'----------------------------------------------

The code probably needs an error handler also.

Just an idea....

HTH
[/QUOTE]

Verification