Hi all, I have the following code:
In both cells z1 and aa1 is 0. When I do a query in the Access database to which this code refers, there are ~78,000 records where L20S = 0 and L20W = 0, yet the rstmeet.recordcount always comes up as 1 record only. Not sure if it's of any relevance but both the L20STARTS field nor the L20WINS field are NOT indexed, does that make any difference?
Any ideas why this might be? Have examined the code back to front but can't work out why.
Code:
Sub getoverall()
Sheets("overall record").Select
Calculate
Range("ad2:ad3").ClearContents
Dim sql_st As String
aruns = ActiveSheet.Range("z1").Value
awins = ActiveSheet.Range("aa1").Value
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsfile = wrkjet.OpenDatabase("c:\research IV.mdb")
sql_st = "Select * FROM data Where L20S = " & aruns & " AND L20W = " & awins & ""
Set rstmeet = dbsfile.OpenRecordset(sql_st, dbOpenDynaset)
If rstmeet.RecordCount > 0 Then
With rstmeet
Range("ad3").Value = rstmeet.RecordCount
End With
End If
End sub
In both cells z1 and aa1 is 0. When I do a query in the Access database to which this code refers, there are ~78,000 records where L20S = 0 and L20W = 0, yet the rstmeet.recordcount always comes up as 1 record only. Not sure if it's of any relevance but both the L20STARTS field nor the L20WINS field are NOT indexed, does that make any difference?
Any ideas why this might be? Have examined the code back to front but can't work out why.