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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sxxguptaAuthor Commented:
sxxguptaAuthor Commented:
Thanks Dale.
Dale FyeOwner, Developing Solutions LLCCommented:
glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.