How I used the CalculateState function

It's awful, and I'm glad I don't have to follow this any further. By the way, if you see any routines you would like to see elucidated, please let me know. Sub Check( ByVal theTable, ByVal theDate, ByRef theConn ) Dim oR1, oR2, oR3, oR4 Dim aDate1, aDate2, aDate3, aDate4 Dim kw1, kw2, kw3, kw4 Dim theRule Dim theDateComponents theDate = DateComponents( theDate ) Select Case Year( theDate ) Case 2001 aDate1 = DateAdd( "yyyy", 1, theDate ) '2002 OpenRecordSet2 theConn, oR1, SelectKW( theTable, Year( aDate1 ), Month( aDate1 ), Day( aDate1 ), Hour( aDate1 ), Minute( aDate1 ), 0 ) If oR1.RecordCount > 0 Then kw1 = oR1("kw") Else kw1 = 0 End If aDate2 = DateAdd( "yyyy", 2, theDate ) '2003 OpenRecordSet2 theConn, oR2, SelectKW( theTable, Year( aDate2 ), Month( aDate2 ), Day( aDate2 ), Hour( aDate2 ), Minute( aDate2 ), 0 ) If oR2.RecordCount > 0 Then kw2 = oR2("kw") Else kw2 = 0 End If aDate3 = DateAdd( "yyyy", 3, theDate ) '2004 OpenRecordSet2 theConn, oR3, SelectKW( theTable, Year( aDate3 ), Month( aDate3 ), Day( aDate3 ), Hour( aDate3 ), Minute( aDate3 ), 0 ) If oR3.RecordCount > 0 Then kw3 = oR3("kw") Else kw3 = 0 End If aDate4 = DateAdd( "yyyy", 4, theDate ) '2005 OpenRecordSet2 theConn, oR4, SelectKW( theTable, Year( aDate4 ), Month( aDate4 ), Day( aDate4 ), Hour( aDate4 ), Minute( aDate4 ), 0 ) If oR4.RecordCount > 0 Then kw4 = oR4("kw") Else kw4 = 0 End If Case 2002 aDate1 = DateAdd( "yyyy", -1, theDate ) '2001 OpenRecordSet2 theConn, oR1, SelectKW( theTable, Year( aDate1 ), Month( aDate1 ), Day( aDate1 ), Hour( aDate1 ), Minute( aDate1 ), 0 ) If oR1.RecordCount > 0 Then kw1 = oR1("kw") Else kw1 = 0 End If aDate2 = DateAdd( "yyyy", 1, theDate ) '2003 OpenRecordSet2 theConn, oR2, SelectKW( theTable, Year( aDate2 ), Month( aDate2 ), Day( aDate2 ), Hour( aDate2 ), Minute( aDate2 ), 0 ) If oR2.RecordCount > 0 Then kw2 = oR2("kw") Else kw2 = 0 End If aDate3 = DateAdd( "yyyy", 2, theDate ) '2004 OpenRecordSet2 theConn, oR3, SelectKW( theTable, Year( aDate3 ), Month( aDate3 ), Day( aDate3 ), Hour( aDate3 ), Minute( aDate3 ), 0 ) If oR3.RecordCount > 0 Then kw3 = oR3("kw") Else kw3 = 0 End If aDate4 = DateAdd( "yyyy", 3, theDate ) '2005 OpenRecordSet2 theConn, oR4, SelectKW( theTable, Year( aDate4 ), Month( aDate4 ), Day( aDate4 ), Hour( aDate4 ), Minute( aDate4 ), 0 ) If oR4.RecordCount > 0 Then kw4 = oR4("kw") Else kw4 = 0 End If Case 2003 aDate1 = DateAdd( "yyyy", -2, theDate ) '2001 OpenRecordSet2 theConn, oR1, SelectKW( theTable, Year( aDate1 ), Month( aDate1 ), Day( aDate1 ), Hour( aDate1 ), Minute( aDate1 ), 0 ) If oR1.RecordCount > 0 Then kw1 = oR1("kw") Else kw1 = 0 End If aDate2 = DateAdd( "yyyy", -1, theDate ) '2002 OpenRecordSet2 theConn, oR2, SelectKW( theTable, Year( aDate2 ), Month( aDate2 ), Day( aDate2 ), Hour( aDate2 ), Minute( aDate2 ), 0 ) If oR2.RecordCount > 0 Then kw2 = oR2("kw") Else kw2 = 0 End If aDate3 = DateAdd( "yyyy", 1, theDate ) '2004 OpenRecordSet2 theConn, oR3, SelectKW( theTable, Year( aDate3 ), Month( aDate3 ), Day( aDate3 ), Hour( aDate3 ), Minute( aDate3 ), 0 ) If oR3.RecordCount > 0 Then kw3 = oR3("kw") Else kw3 = 0 End If aDate4 = DateAdd( "yyyy", 2, theDate ) '2005 OpenRecordSet2 theConn, oR4, SelectKW( theTable, Year( aDate4 ), Month( aDate4 ), Day( aDate4 ), Hour( aDate4 ), Minute( aDate4 ), 0 ) If oR4.RecordCount > 0 Then kw4 = oR4("kw") Else kw4 = 0 End If Case 2004 aDate1 = DateAdd( "yyyy", -3, theDate ) '2001 OpenRecordSet2 theConn, oR1, SelectKW( theTable, Year( aDate1 ), Month( aDate1 ), Day( aDate1 ), Hour( aDate1 ), Minute( aDate1 ), 0 ) If oR1.RecordCount > 0 Then kw1 = oR1("KW") Else kw1 = 0 End If aDate2 = DateAdd( "yyyy", -2, theDate ) '2002 OpenRecordSet2 theConn, oR2, SelectKW( theTable, Year( aDate2 ), Month( aDate2 ), Day( aDate2 ), Hour( aDate2 ), Minute( aDate2 ), 0 ) If oR2.RecordCount > 0 Then kw2 = oR2("kw") Else kw2 = 0 End If aDate3 = DateAdd( "yyyy", -1, theDate ) '2003 OpenRecordSet2 theConn, oR3, SelectKW( theTable, Year( aDate3 ), Month( aDate3 ), Day( aDate3 ), Hour( aDate3 ), Minute( aDate3 ), 0 ) If oR3.RecordCount > 0 Then kw3 = oR3("kw") Else kw3 = 0 End If aDate4 = DateAdd( "yyyy", 1, theDate ) '2005 OpenRecordSet2 theConn, oR4, SelectKW( theTable, Year( aDate4 ), Month( aDate4 ), Day( aDate4 ), Hour( aDate4 ), Minute( aDate4 ), 0 ) If oR4.RecordCount > 0 Then kw4 = oR4("kw") Else kw4 = 0 End If Case 2005 aDate1 = DateAdd( "yyyy", -4, theDate ) '2001 OpenRecordSet2 theConn, oR1, SelectKW( theTable, Year( aDate1 ), Month( aDate1 ), Day( aDate1 ), Hour( aDate1 ), Minute( aDate1 ), 0 ) If oR1.RecordCount > 0 Then kw1 = oR1("kw") Else kw1 = 0 End If aDate2 = DateAdd( "yyyy", -3, theDate ) '2002 OpenRecordSet2 theConn, oR2, SelectKW( theTable, Year( aDate2 ), Month( aDate2 ), Day( aDate2 ), Hour( aDate2 ), Minute( aDate2 ), 0 ) If oR2.RecordCount > 0 Then kw2 = oR2("kw") Else kw2 = 0 End If aDate3 = DateAdd( "yyyy", -2, theDate ) '2003 OpenRecordSet2 theConn, oR3, SelectKW( theTable, Year( aDate3 ), Month( aDate3 ), Day( aDate3 ), Hour( aDate3 ), Minute( aDate3 ), 0 ) If oR3.RecordCount > 0 Then kw3 = oR3("kw") Else kw3 = 0 End If aDate4 = DateAdd( "yyyy", -1, theDate ) '2004 OpenRecordSet2 theConn, oR4, SelectKW( theTable, Year( aDate4 ), Month( aDate4 ), Day( aDate4 ), Hour( aDate4 ), Minute( aDate4 ), 0 ) If oR4.RecordCount > 0 Then kw4 = oR4("kw") Else kw4 = 0 End If End Select Set oR1 = Nothing Set oR2 = Nothing Set oR3 = Nothing Set oR4 = Nothing theRule = "R" & Year( theDate ) & "_" & calculateState( kw1, kw2, kw3, kw4 ) Select Case Year( theDate ) Case 2001 Report reportFile, Join( Array( theDate, "*",kw1,kw2,kw3,kw4, theRule ), vbTab ) Case 2002 Report reportFile, Join( Array( theDate, kw1,"*",kw2,kw3,kw4, theRule), vbTab ) Case 2003 Report reportFile, Join( Array( theDate, kw1,kw2,"*",kw3,kw4, theRule), vbTab ) Case 2004 Report reportFile, Join( Array( theDate, kw1,kw2,kw3,"*",kw4, theRule), vbTab ) Case 2005 Report reportFile, Join( Array( theDate, kw1,kw2,kw3,kw4,"*", theRule), vbTab ) End Select End Sub