Not to include Weekends

[Stemdriller]

I know this topic has been extensivley written about but I could do with some guidance.

I have a form with about 10 textboxes on. The first textbox gets it's date from the Release Date, less 30 days. The other textboxes get there dates from similar offsets off other textboxes cascading down.

eg Text2 = Text1 less 4 days
Text3 = Text less 5 days etc etc

And yes I need to omit weekends and holidays.

I am trying to get the following working

Public Function wdateadd(Startdate As Date, wdays As Integer)
Dim h As Integer
Dim w As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = Currentdb
Set rst = DB.OpenRecordset("SELECT [Date] FROM tblBankHoliday", dbOpenSnapshot)
w = 0
h = 0
Do Until h = wdays
mydate = DateAdd("d", w, Startdate)
rst.FindFirst "[Date] = #" & mydate & "#"
If Weekday(mydate) <> vbSunday And Weekday(mydate) <> vbSaturday Then
If rst.NoMatch Then
h = h + 1
End If
End If
w = w + 1
Loop

wdateadd = DateAdd("d", w, Startdate)

End Function

I have created a tblHolidays

My main issue is that I simply don't no how to implement this code.
I'm not particulary good with vba, i get by, just.

If one of you kind souls could explain how to get Text1 and Text2 working together I can then sort the rest out.

At your mercy

Gareth

 

[jzwp22]

You will need to call the function in an expression in the control source for the textbox

text2=wdateadd([text1], 4)

...but the function you posted will only work for days forward in time so it will add 4 days to the date held in text1, so you will have to modify the function so that it knows which way to go: forward or backwards in time.

 

[raskew]

Hi -

The following will allow you to either or subtract business days (Monday - Friday). It doesn't, as written, include provisions for holidays:

Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer

    dteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold), 1)
       Else          'subtract days
          dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay(dteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = dteHold
    
End Function

'*******************************************

HTH - Bob

 

[Stemdriller]

Hi jzwp22

Yes, that does indeed work, many thanks.

I have tried to make it go back but alas got dates from 1783.

So my question what do I amend to make the Function count backwards??

Gareth

 

[Stemdriller]

Hi jzwp22

Yes, that does indeed work, many thanks.

I have tried to make it go back but alas got dates from 1783.

So my question what do I amend to make the Function count backwards??

Gareth

Sorry sorted it. DOH!

 

[jzwp22]

Glad to hear that you got it sorted out. Good luck with your database.