OK tried the SQL statement but got the following error;
At most one record can be returned by this subquery. (Error 3354)
I have tried calling a function to loop through the data and increment a counter but it doesn't seem to work, here is the code;
- Public Function ConsecutiveRecs(ByVal strComponent As Variant) As String
-
-
Dim RS As New ADODB.Recordset
-
Dim strExceptionLevel As String
-
Dim strAssetComponent As String
-
Dim strSQL As String
-
Dim intCounter As Long
-
-
-
' If IsNull(str) Then Exit Function
-
-
intCounter = 0
-
strExceptionLevel = "Initial"
-
strAssetComponent = "New"
-
-
strSQL = "SELECT Asset,Result, Date from RESULTS WHERE Asset'" & _
-
strComponent & "' ORDER BY Asset, Date;"
-
-
-
Set rs1 = Nothing
-
With RS
-
'----------- Open recordset to get records
-
.ActiveConnection = CurrentProject.Connection
-
.CursorType = adOpenForwardOnly
-
.LockType = adLockReadOnly
-
.Open strSQL
-
-
'--------loop through retrieved records
-
While Not .EOF
-
-
-
If strAssetComponent = ![Asset] And strExceptionLevel = ![Result] Then
-
intCounter = intCounter + 1
-
ConsecutiveRecs = intCounter
-
'---- respond with the count
-
Else
-
If strAssetComponent <> ![Asset] Then _
-
intCounter = 0
-
ConsecutiveRecs = intCounter
-
'---- reset the counter and respond with the count
-
-
End If
-
'---store current asset and component combination and exception level to compare with the next one (see above)
-
strExceptionLevel = ![Result]
-
strAssetComponent = ![Asset]
-
.MoveNext
-
-
Wend
-
.Close
-
End With
-
-
Set RS = Nothing
-
-
End Function
I am calling this from the following query;
SELECT DISTINCT Asset , Result, Date, ConsecutiveRecs(Asset) AS Consecutives
FROM RESULTS
GROUP BY Asset, Result, Date;
The result of this is an error, INVALID USE OF NULL at the line strExceptionLevel = ![Result] any ideas?
