add working days in access 2007

[dav pea]

Ok, so I've seen this question asked before but I cant seem to adapt the solution/s provided to my particular case.

I have to record the date an item goes out to a suncontractor, and the date it comes back in, and compare these dates to the due date to give an idea whether the subcons are giving appropriate service.
So far so good.

I have my subcontractors listed in a seperate linked table next to the lead times for each one. This lead time autopopulates on the data entry form and is added to the Outgoing date to give a Due date that I may compare to the In date.

Now in principle this is quite simple, and I have done this, but heres the problem...

The lead times are in working days, ie; monday to friday, so what I want is
1) the Due date to be calculated based only on working days,
and 2) for the due date to only fall on a working day during the week (if possible).

example; If a job goes out on a tuesday, and has a lead time of 5 days, then instead of giving the Due date to be Sunday and counting the job late when it comes in on Monday, the due date should be the following Tuesday, and the job is in fact on time.

I have everything else running very smoothly atm, and this is the final thing thats driving me wild!

Any help that anyone could give would be greatly appreciated, I can't believe this isnt a built in function to Access!

[NeoPa]

Dav Pea:
I can't believe this isnt a built in function to Access!

It is, but we'll get to that.

Let's start from the beginning and get things properly specified before looking for solutions. Can you confirm, before we go any further with this, that weekend dates are the only dates that need to be excluded in the logic. If so then it's relatively straightforward. If not then it's a whole different ball game.

Assuming for now then, that we have the question right and there are no requirements to avoid special holiday dates etc, then there is a built-in facility to handle this for you. Unfortunately, it doesn't work! Caveat: It hasn't worked in any version I've tested it on, but I wrote my own routine to handle it so don't test it regularly.

DateAdd() has an option to move backwards or forwards by weekdays (DateAdd("w",1,Date())). I just checked it in Access 2003 by running the following code from the Immediate Pane :

For X = 1 To 6 : ?DateAdd("w",X,Date()) : Next X
08/09/2011
09/09/2011
10/09/2011
11/09/2011
12/09/2011
13/09/2011

The results show that weekend dates were included with all the others :-(

The MoveWD() function was developed to provide this functionality.

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (Weekday(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

[dav pea]

Ah right... now firstly thank you very much for a quick response, I didn't expect that!

I feel a bit dense in saying i'm not quite sure in how to go about implementing that solution. I can confirm that I only need weekends excluded, i'd rather not open the can of worms that is holidays and our business runs most holidays anyway.

How would I go about using that code? In particular I need it to get a lead time from a table, which it can reference, and then add said lead time to the date autopopulated on the form, to give the due date. Everything I know about VBA I learned on the fly while doing this project, which is to say I know very little!

So far my interim solution is to just add 2 days on for every 5 days of lead time, in order to give a worst case scenario of lateness, but obviously this isnt ideal.

I apologize for asking for clarification, you have most likely fixed my problem, I am just not sure of how to implement the solution!

Thanks again.

[NeoPa]

No need to apologise. The most important thing for me is clarity of communication and the member's current ability is almost irrelevant.

I'm still not absolutely clear where you want to call this from, but regardless of where that is you simply use the coding :

MoveWD([YourDate], X)

[YourDate] is used in the example as a field from your record source, but could be used in code with a Date variable or any expression that resolves to a valid date.
X can be an integer value or variable. This can even be a field from your record source if run within a SQL query.

If you try this out and find it doesn't work for any reason then post what you have tried and we'll see if we can find and fix the problem for you.

To get the code into a usable state in your project you need to paste it into a Standard Module in your project. If your project doesn't yet have one, or you choose to enter it into a new one anyway, then do the following (New or existing you should read Require Variable Declaration first) :

1. Open/Switch to the VBA IDE (Code development window) using Alt-F11 from Access.
2. Make sure the Project Explorer pane is open and selected (Ctrl-R).
3. Navigate the tree and see if a Modules branch exists.
   At this point you can find any existing Standard Modules that already exist.
4. We are creating a new one so the next step is to right-click with the mouse within this (Project Explorer) pane and select Insert and Module.
5. You should now have a stub open looking like :
   

   Option Compare Database
   Option Explicit

6. Paste the code for MoveWD() into this module and save it. It should prompt you for a module name so assign it one. I normally use the form modXXX so any references to it anywhere in code are easier to recognise and understand (They're clearly dealing with a module object).

[dav pea]

It's....it's so beautiful. Works perfectly. I cannot thank you enough, been getting really frustrated over this, and to have it work so well just like that has really cheered me up!

You guys really aren't getting enough credit, that was clear and concise.

Again, many thanks for a speedy resolution!

[NeoPa]

It's a pleasure to help Dav. Hopefully others in a similar position will find it equally helpful :-)