Help adding 3 UK working days (excluding weekends and bank holidays) to a form field please?

[RockChick]

Hi - I have never used a forum before so please forgive any newbie errors!

I would like some help please. I have a Complaints database which I have inherited from a previous team, but I have managed to customise it to do what I require - all except one thing. I have a field which contains the date on which the complaint was received [ReceivedbyCCG]. I want to add three working days to this and to then populate another field [AcknowledgementDue]. I am not at all experienced in Access, and most of what I have done so far has been by trial and error, and google, but having looked at various threads I think I need to add some sort of code. I have no clue how or where to do this. I have created a table of all the dates for the next 4 years (excluding the weekends and bank holidays) as partially shown in the snippet below, which adds the three days, but then I am stuck. I have added a screen shot of the fields in orange below too. The ReceivedbyCCG date is added by the user, as is the AckSent date, and I just want to make the Acknowledgement due box populate with the additional 3 working days. I can make it work for actual days with =DateAdd("w",3,[ReceivedbyCCG]) but not for working days.
Is there anyone who could explain in simple terms what I need to do and talk me through how I should do this please?


Any assistance would be much appreciated.
Thank You.
RockChick

[Micron]

In spite of that excellent post I can't really tell what the problem is. It looks like your expression is adding 3 weeks, not 3 days but you didn't say what result you were getting. Is this one of the sources you looked at for this issue?
https://stackoverflow.com/questions/...g-bank-holiday

[davegri]

@Micron, 'w' is weekday, not week.

@Rockchick, This code works in a test setup. If you need a simplified db example, I can post that.

Option Compare Database
Option Explicit


Sub ReceivedByCCG_AfterUpdate()
    Dim i As Long
    If (ReceivedByCCG & "") = "" Then Exit Sub
    For i = 3 To 6
    Debug.Print DateAdd("d", i, ReceivedByCCG)
        If DCount("*", "tblCalendar", "CalDate =#" & DateAdd("d", i, ReceivedByCCG) & "#") > 0 Then
            Me.[AcknowledgementDue] = DateAdd("d", i, [ReceivedByCCG])
            Exit For
        End If
    Next i
End Sub

[RockChick]

@davegri - thank you that looks great, and thank you for using the names I had used - that makes it so much easier to follow. Could you please explain where I need to add this code in please? Do I need to add it as an EventProcedure from the Field properties and then type into the VB window that appears? Sorry, I really have very little experience of this but am very willing to be guided, and appreciate the expertise available. :-)

[Micron]

I guess when it comes to IIRC, I didn't. Can't say "If memory serves..." either.

[davegri]

@davegri - thank you that looks great, and thank you for using the names I had used - that makes it so much easier to follow. Could you please explain where I need to add this code in please? Do I need to add it as an EventProcedure from the Field properties and then type into the VB window that appears? Sorry, I really have very little experience of this but am very willing to be guided, and appreciate the expertise available. :-)

That sounds right. And you don't need to type it in, copy/paste works from the screen here.

[Gicu]

I am attaching a module I have used for many years for working with dates, extract and import it in your database. Then you can start using the functions in conjunction with your own bank holiday table by providing the holiday array:

Dim rstData    As DAO.Recordset
Dim v          As Variant


Set rstData = CurrentDb.OpenRecordset("Select CalDate From tblCalendar Where CalDate >= #" & Me.AckSent & "#;")
rstData.MoveLast
rstData.MoveFirst  
v = rstData.GetRows(rstData.RecordCount)

Me.AcknowledgementDue=dhAddWorkDaysA(Me.AckSent,v)

Cheers,