Personally - I think you should only show the minimum limits in the tblRules table as follows (using your examples from above):
One way could be:
This gets all of the MinRules which are greater than or equal to the one you're looking for - but orders it by MinRule. Therefore - the first one you retrieve from the recordset will be the category (level) that you want to use.
Dim rsMinRule as Recordset
Dim strCodeToUse as String
Set rsMinRule = CurrentDb.OpenRecordset("SELECT Code FROM tblRules WHERE MinRule <= " & intCountOfRSA & " ORDER BY MinRule;")
If Not rsMinRule.EOF Then
strCountToUse = rsMinRule!Code
Another (pure SQL) way may be:
This one does the same as the VB code above - but does it without having to select all of the rows from the tblRules table. It may be a little more efficient - depending on how many rows you'll have in tblRules.
strSQL = "SELECT Code WHERE MinRule = (SELECT Max(MinRule) FROM tblRules WHERE MinRule <= " & intCountOfRFA & ")"
Last edited by joeldixon66; 05-15-04 at 13:04.