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

Toward a state table generator (VBScript)

This bit of code I used to create a two-state table for 5 variables. It would have been fairly easy to add more states -- by changing the if to a select case in 'state' and giving the for loops more things to loop through -- and to add more variables -- by specifying another loop and and another call to 'state'.

I won't be writing the generator ... the whole need for state tables disappeared in a meeting earlier today. function dq( txt ) dq = chr(34) & txt & chr(34) end function function state( num,vlu ) if vlu = 0 then state = dq("v" & num & "=0" ) else state = dq("v" & num & ">0") end if end function f = "Array( _" & vbCRLF for a = 0 to 1 for b = 0 to 1 for c = 0 to 1 for d = 0 to 1 for e = 0 to 1 f = f & "Array(" & _ join( _ array( _ state(1,a), _ state(2,b), _ state(3,c), _ state(4,d), _ state(5,e) _ ), ", " _ ) & "), _" & vbCRLF next next next next next f = f & ")" f = replace( f, ", _" & vbCRLF & ")", " _" & vbCRLF & ")" ) wscript.echo f

Found another use for Eval() in VBScript - state tables

Imagine trying to keep track of all possible combinations of four variables. Big if/then/else/end if, right? Not necessarily. function calculateState( v1, v2, v3, v4 ) dim stateTable dim stateItem dim stateLoop dim stateRule stateTable = Array( _ Array( "v1=0", "v2=0", "v3=0", "v4=0" ), _ Array( "v1>0", "v2=0", "v3=0", "v4=0" ), _ Array( "v1=0", "v2>0", "v3=0", "v4=0" ), _ Array( "v1>0", "v2>0", "v3=0", "v4=0" ), _ Array( "v1=0", "v2=0", "v3>0", "v4=0" ), _ Array( "v1>0", "v2=0", "v3>0", "v4=0" ), _ Array( "v1=0", "v2>0", "v3>0", "v4=0" ), _ Array( "v1>0", "v2>0", "v3>0", "v4=0" ), _ Array( "v1=0", "v2=0", "v3=0", "v4>0" ), _ Array( "v1>0", "v2=0", "v3=0", "v4>0" ), _ Array( "v1=0", "v2>0", "v3=0", "v4>0" ), _ Array( "v1>0", "v2>0", "v3=0", "v4>0" ), _ Array( "v1=0", "v2=0", "v3>0", "v4>0" ), _ Array( "v1>0", "v2=0", "v3>0", "v4>0" ), _ Array( "v1=0", "v2>0", "v3>0", "v4>0" ), _ Array( "v1>0", "v2>0", "v3>0", "v4>0" ) _ ) stateRule = -1 For stateLoop = 0 To 15 stateItem = stateTable( stateLoop ) If Eval( stateItem( 0 ) ) And _ Eval( stateItem( 1 ) ) And _ Eval( stateItem( 2 ) ) And _ Eval( stateItem( 3 ) ) Then stateRule = stateLoop Exit For End If Next calculateState = stateRule End Function WScript.Echo calculateState( 0, 0, 1, 1 )