ms access dcount function help

The name of my query is "release_test_qry" with the following two columns (Count1 and x3).  I need help in using the dcount function to count the consecutive "1" from the x3 column so that the x3count columns shows the results as below

Count1      x3           x3count
1               2               0      
2               3               0                   
3               1                0                            
4               5                0
5               2               0      
6               1               0      
7               4               0      
8               4               0      
9               1               3                                  'NOTE HERE WE HAVE 3 BECAUSE THERE ARE 3 "1" IN A ROW
10               1               0      
11               1               0      
12               4               0      
13               3               0      
14               2                0
15               2                0
16               1               0      

Who is Participating?
Dale FyeCommented:
Your sample solution is incorrect as there are consecutive values on lines 7/8 and 15.

You are not going to be able to use DCOUNT or any of the other domain functions for this, because there is no way to identify how many consecutive values have the same number.  You are going to have to use a function, see below.

I've used a static database and recordset so that each call to the function does not require opening the table over and over again.  This also provides a way to keep the record pointer at the first record which did not equal the TestVal (X3) value you passed, ensuring that you don't have values like:

10            1               3
11            1               2
12            1               1

However, the downside of keeping these variables as static, is that each time you run this query, you must also (after running the query) call the function with a CloseOut (3rd argument) value of True in order to release the recordset and database objects.  Failure to do so would cause that recordset and database object to remain open until your application closes.

To call this function, you would use:

SELECT Count1, X3, ConsecutiveValues([Count1], [X3]) as X3Count
FROM yourTable

Note that you will have to change to "yourTable" in line 18 of the function to the name of your table

Public Function ConsecutiveValues(RowID As Long, TestVal As Long, Optional CloseOut As Boolean = False) As Long

    Static db As dao.Database
    Static rs As dao.Recordset

    ConsecutiveValues = 0

    'closeout the recordset and db objects if CloseOut = True
    'Otherwise, instantiate db and rs if they have not already been instantiated
    If CloseOut = True Then
         On Error Resume Next
         Set rs = Nothing
         Set db = Nothing
         Exit Function
     ElseIf (db Is Nothing) Or (rs Is Nothing) Then
          Set db = CurrentDb
          Set rs = db.OpenRecordset("SELECT * FROM yourTable Order By Count1 ")
    End If

    'this would handle rows 10 and 11 sample data
    If RowID <= rs.AbsolutePosition Then Exit Function

    'Reposition the pointer to the record after the RowID
    rs.FindFirst "Count1 = " & RowID
    If rs.NoMatch Then
        Exit Function
    ElseIf rs.EOF Then
        Exit Function
            If rs.EOF Then Exit Do
            If (rs!X3 = TestVal) Then
                ConsecutiveValues = ConsecutiveValues + 1
                Exit Do
            End If
        If ConsecutiveValues > 0 Then ConsecutiveValues = ConsecutiveValues + 1
        Exit Function
    End If

End Function

Open in new window

sxxguptaAuthor Commented:
sxxguptaAuthor Commented:
Thanks Dale.
Dale FyeCommented:
glad I could help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.