Counting Rolling Days (1 Viewer)

VijayR · Oct 21, 2017

Hi All,
I am looking for some help. I have a table that contains a list of account numbers with dates.
I have been asked to calculate consecutive days including weekends.
E.g

Account Number Dates Day Count
ABC 01/02/2017 1
ABC 02/02/2017 2
ABC 10/02/2017 1
XYZ 01/02/2017 1
XYZ 08/02/2017 1
ABC 01/04/2017 1

I was looking at the DateDiff function, and putting this into loop
E.g

For I = 1 to rs.RecordSet.Count

Using the DateDiff function by comparing the previous date with the next date in the table by

rs.MoveNext

Next i

I also need to include any holidays, however I have set a holiday table.
Any help would be very much appreciated.

Thanks and Regards

arnelgp · Oct 21, 2017

you don't need to store the results
in the table. you only need it to show
in a query. but you need to create a function to
do this.

paste the following code in a Standard Module
in VBE:

Code:

Public Function CountConsecutiveDays(actNo As Variant, _
                                     dt As Variant, _
                                     strAccNoFieldName As String, _
                                     strDateFieldName As String, _
                                     strTableName As String) As Integer
    Dim rs As DAO.Recordset
    Dim prevDate As Date
    Dim counter As Integer
    dt = CDate("0" & dt)
    Set rs = DBEngine(0)(0).OpenRecordset( _
        "Select " & strDateFieldName & " From " & strTableName & " Where " & _
        strAccNoFieldName & " = " & Chr(34) & actNo & Chr(34) & " And " & _
        strDateFieldName & "<= #" & Format(dt, "mm/dd/yyyy") & "# " & _
        "Order By " & strDateFieldName & " ASC;")
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        counter = 1
        prevDate = .Fields(strDateFieldName)
        .MoveNext
        While Not .EOF
            If prevDate < .Fields(strDateFieldName) Then
                If .Fields(strDateFieldName) - prevDate = 1 Then
                    counter = counter + 1
                Else
                    counter = 1
                End If
                prevDate = .Fields(strDateFieldName)
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    CountConsecutiveDays = counter
End Function

now Create a Query against your Table:

SELECT [Account Number], [Dates], CountConsecutiveDays([Account Number],[Dates],"[Account Number]","[Dates]","[yourTableName]") AS [Day Count]
FROM [yourTableName] ORDER BY [Account Number], [Dates] ASC;

***
Replace all fields and table name in the query with
correct field names and table name.

VijayR · Oct 21, 2017

Wow thank you so much i will give this go today,

VijayR · Oct 21, 2017

Hiya,
Would it be possible to explain the following line of code?
[FONT=&quot] Set rs = DBEngine(0)(0).OpenRecordset( _[/FONT]
[FONT=&quot] "Select " & strDateFieldName & " From " & strTableName & " Where " & _[/FONT]
[FONT=&quot] strAccNoFieldName & " = " & Chr(34) & actNo & Chr(34) & " And " & _[/FONT]
[FONT=&quot] strDateFieldName & "<= #" & Format(dt, "mm/dd/yyyy") & "# " & _[/FONT]
[FONT=&quot] "Order By " & strDateFieldName & " ASC;")[/FONT]

I would like to expand my knowledge on MS Access, I have used VBA in Excel but not so much MS Access database environment.

Also you mentioned that I should setup query, i.e. therefore do I go to create query design and then add the table that I would like perform that task on?

Thanks and Regards

VijayR · Oct 28, 2017

Thank you for reply, however i have been unable to get it to work, if you explain in more details then perhaps i can get it work. Thank you

arnelgp · Oct 28, 2017

There is an Explanation module i made open it in vba editor. See sample rolling days query

VijayR · Oct 28, 2017

Hiya, i am going to study this. Thank you soooo much or your help.
Ps I am able to write in VBA in Excel, the thing is that when i apply same logic, well its not the same.
Thank you

arnelgp · Oct 28, 2017

Goodluck with ur proj

VijayR · Oct 28, 2017

Do i need to mark this somehow as resolved?
With what you have given me, i will be able to add the next step in myself.

It has also answered another question i had and i can see why and where i was wrong

arnelgp · Oct 28, 2017

You may so others will have a chance to view this on similar situation. If you have more issues create new thread.

Counting Rolling Days (1 Viewer)

VijayR

New member

arnelgp

..forever waiting... waiting for jellybean!

VijayR

New member

VijayR

New member

VijayR

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

VijayR

New member

arnelgp

..forever waiting... waiting for jellybean!

VijayR

New member

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread