UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pulling A Range Of Dates In Access And Skipping Holidays, Access 2013    
 
   
Koehler
post Jan 10 2017, 04:36 PM
Post#1



Posts: 2
Joined: 10-January 17



I am trying to Skip holidays using VBA code. I have the code written and i have it work with the weekday so I am only pulling Monday Through Friday. The code is meant to pull Prior day totals so i am always 1 day behind but my problem is when there is a Holiday the code skips the holiday leaving it all 0's what i need it to do is negate the day all together.

The Code is going step by step through the code looking for how many records is in a range i am collecting 7 so far for the whole week, but if a holiday is included which there is a possibility we can have 2 holidays in a range it displays those days as records ( I enter empty records in the DB). is there a way i can just negate nulls and have it skip nulls in the database where weekdays should be or can i create a subroutine to pull from a table that has holidays on it and just skips those days still displaying 5 business days.


CODE
    Dim iWeekDay As Integer
Dim sSQL As String

Dim objRecordset As ADODB.Recordset
Dim objRecordsetConn As ADODB.Connection
Dim objRecordsetComm As ADODB.Command

iWeekDay = WeekDay(Date)



Set objRecordsetConn = New ADODB.Connection
objRecordsetConn.ConnectionString = CurrentProject.Connection
objRecordsetConn.Open

'Start new section Collections

sSQL = "Select CollDate From tblTXandVACollDist Where CollDate Between date()-7 and date()-1 Group by CollDate Order by CollDate;"

Set objRecordsetComm = New ADODB.Command
    objRecordsetComm.ActiveConnection = objRecordsetConn
    objRecordsetComm.CommandText = sSQL
    objRecordsetComm.CommandType = adCmdText

    'Open ADO recordset
    Set objRecordset = New ADODB.Recordset
    objRecordset.ActiveConnection = objRecordsetConn
    Set objRecordset.Source = objRecordsetComm
    objRecordset.CursorLocation = adUseClient
    objRecordset.CursorType = adOpenStatic    'Allows both forward and backward movement in recordset
    objRecordset.LockType = adLockPessimistic 'Allows records to be edited.
    objRecordset.Open
    'objRecordset.MoveLast
    'objRecordset.MoveFirst

If objRecordset.RecordCount >= 1 Then
            dCollDate1 = objRecordset("CollDate")

        Else
            Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
                Case 2   ' This is if the current day is monday
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 3
                Case 3 ' This is if the current day is Tue
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 1
                Case 4   ' This is if the current day is Wend
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 5    ' This is if the current day is Thur
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 6  ' This is if the current day is Fri
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 4
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
            End Select

        End If

        If objRecordset.RecordCount >= 2 Then
            objRecordset.MoveNext

            dCollDate2 = objRecordset("CollDate")

        Else
        Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
                Case 2   ' This is if the current day is monday
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 3
                Case 3 ' This is if the current day is Tue
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 1
                Case 4   ' This is if the current day is Wend
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 5    ' This is if the current day is Thur
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 6  ' This is if the current day is Fri
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 4
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
            End Select

        End If

        If objRecordset.RecordCount >= 3 Then
            objRecordset.MoveNext


            dCollDate3 = objRecordset("CollDate")

        Else
            Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
                Case 2   ' This is if the current day is monday
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 3
                Case 3 ' This is if the current day is Tue
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 1
                Case 4   ' This is if the current day is Wend
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 5    ' This is if the current day is Thur
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 6  ' This is if the current day is Fri
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 4
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
            End Select

        End If

        If objRecordset.RecordCount >= 4 Then
            objRecordset.MoveNext

            dCollDate4 = objRecordset("CollDate")


        Else

        Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
                Case 2   ' This is if the current day is monday
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 3
                Case 3 ' This is if the current day is Tue
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 1
                Case 4   ' This is if the current day is Wend
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 5    ' This is if the current day is Thur
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 6  ' This is if the current day is Fri
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 4
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
            End Select

        End If

        If objRecordset.RecordCount = 5 Then 'this is a test portion to see if i have to adjust the chart to make sure the chart works on an individual basis per day
            objRecordset.MoveNext

            dCollDate5 = objRecordset("CollDate")


        Else

        Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
                Case 2   ' This is if the current day is monday
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 3
                Case 3 ' This is if the current day is Tue
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 4
                    dCollDate5 = Date - 1
                Case 4   ' This is if the current day is Wend
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 5
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 5    ' This is if the current day is Thur
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 6
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
                Case 6  ' This is if the current day is Fri
                    dCollDate1 = Date - 7
                    dCollDate2 = Date - 4
                    dCollDate3 = Date - 3
                    dCollDate4 = Date - 2
                    dCollDate5 = Date - 1
            End Select


        End If
Go to the top of the page
 
ranman256
post Jan 10 2017, 09:22 PM
Post#2



Posts: 712
Joined: 25-April 14



A table to hold the holidays, tHolidays.
Run a query to count the days in the data table between the dates, qsWorkDaysInDate
Run a query to count the days in tHolidays, between the dates, qsHolidaysInDate
Run a query to subtract qsHolidaysInDate from qsWorkDaysInDate.
Go to the top of the page
 
Koehler
post Jan 11 2017, 11:38 AM
Post#3



Posts: 2
Joined: 10-January 17



is their an example you could provide? as of right now my code will look at the current day and then run through the case statement allowing it to find the prior day.
Go to the top of the page
 
warmlf2
post Jan 11 2017, 02:33 PM
Post#4



Posts: 39
Joined: 19-May 16



I use Arvin Meyer's Routine. It works awesome. Paste the code into a module and make sure you create the holiday table.

YourDate = GetBusinessDay(Nz(YourDateField, #DateIfNull#), Nz(DaysFromOriginalDate, 0))

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application provided author
' is given credit. This code may not be distributed as part of a collection
' without prior written permission. This header must remain intact.


Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Holiday_ID] FROM tblHolidays", dbOpenSnapshot)

If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[Holiday_ID] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

ElseIf intDayAdd < 0 Then

Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[Holiday_ID] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop

End If

GetBusinessDay = datStart

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th May 2017 - 01:33 PM