HELP! AddWorkingDays Function

[philjackson01]

Hi All,

I'm wondering if you could possible help me with my AddWorkingDays function. The issue is that for some reason Access is not recognising 04/05/2015 in my bank holidays table. I'm guessing it is something to do with format of the date, but i have so far come up short with any potential solution.

The function that i am using currently looks like this:

Public Function AddWorkingDays(StartDate As Date, numworkdays As Integer) As Date
    Dim intCount As Integer
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim EndDate As Date
    Dim workdayscount As Integer
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2 WHERE [HolidayDate] > Date()", dbOpenSnapshot)
 
    workdayscount = 0
    EndDate = StartDate
 
    Do While workdayscount < numworkdays
        EndDate = EndDate + 1
        rst.FindFirst "[HolidayDate] = #" & EndDate & "#"
        If rst.NoMatch And Weekday(EndDate) <> vbSunday And Weekday(EndDate) <> vbSaturday Then
            workdayscount = workdayscount + 1
        End If
    Loop
 
    rst.Close
    Set rst = Nothing
    Set db = Nothing
 
    AddWorkingDays = EndDate
End Function

There is then another function that calls this procedure to determine the amount of days added depending on what catergory is selected on the form. A snippet of the code is below:

Select Case frm![Fuel]
    Case 2
        Select Case frm![Category]
            Case 1
                numdays = 0
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for First Visit] = targetdt
                numdays = 5
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for Completion] = targetdt
            Case 2 
                numdays = 5
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for First Visit] = targetdt
                numdays = 15
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for Completion] = targetdt

If i was to use this today and case 2 was selected then the 'Date for First Visit' should be set to 06/05/2015 but for some reason it is set to 05/05/2015.

What confuses me even more is that if i create an imaginary holiday for 29/04/2015 then the 'Date for First Visit' is set 06/05/2015 which is correct as it is 5 working days (excluding today).

Again, if i create an imaginary holiday in the table for 14/05/2015 then it sets the 'SLA Date for Completion' to 20/05/2015, which is indeed 15 working days.

I'm probably doing or missing something stupid, so any help would be much appreicated.

Many thanks,
Phil

 

[pr2-eugin]

Please use Code Tags when posting VBA Code

 

[philjackson01]

Oh yeah sorry about that - all sorted now!

Cheers

 

[JHB]

Try the below:

Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2 WHERE [HolidayDate]>#" & Date() & "#", dbOpenSnapshot)

Or

Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2  WHERE [HolidayDate]>" & Date() , dbOpenSnapshot)

 

[pr2-eugin]

Oh yeah sorry about that - all sorted now!

Cheers

No nothing is "sorted". I take you did not read the second part of the post?

Public Function AddWorkingDays(StartDate As Date, numworkdays As Integer) As Date
    Dim intCount As Integer
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim EndDate As Date
    Dim workdayscount As Integer
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2 WHERE [HolidayDate] > Date()", dbOpenSnapshot)
    
    workdayscount = 0
    EndDate = StartDate
    
    Do While workdayscount < numworkdays
        EndDate = EndDate + 1
        rst.FindFirst "[HolidayDate] = #" & EndDate & "#"
        If rst.NoMatch And Weekday(EndDate) <> vbSunday And Weekday(EndDate) <> vbSaturday Then
            workdayscount = workdayscount + 1
        End If
    Loop
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
    AddWorkingDays = EndDate
End Function

Missing something?

Select Case frm![Fuel]
    Case 2
        Select Case frm![Category]
            Case 1
                numdays = 0
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for First Visit] = targetdt
                numdays = 5
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for Completion] = targetdt
            Case 2 
                numdays = 5
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for First Visit] = targetdt
                numdays = 15
                targetdt = AddWorkingDays(Date, numdays)
                frm![SLA Date for Completion] = targetdt

 

[philjackson01]

Nope definitely missed that - sorry again (newbie).

There is a bit missing but its just a snippet to show how it works.

Thanks

 

[JHB]

Did you read post #4?