Adding Working Days

[Leadparsons]

Hi all,

is the a formula that I can add 10 working days to a date. I have used =WORKDAY(E65,10) in excel but can not work it out in access.

Your help is always appreciated

 

[Joe4]

Are you sure that you copied ALL of the code highlighted in light blue in that link? dhNextWorkdayA is the second UDF in that link:

[COLOR=#008000]' ********* Code Start **************
'
' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
'[/COLOR]

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
   [COLOR=#008000] ' Add the specified number of work days to the
    ' specified date.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' In:
    '   lngDays:
    '       Number of work days to add to the start date.
    '   dtmDate:
    '       date on which to start looking.
    '       Use the current date, if none was specified.
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value, if that's what you want.
    ' Out:
    '   Return Value:
    '       The date of the working day lngDays from the start, taking
    '       into account weekends and holidays.
    ' Example:
    '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
    '   returns #2/25/2000#, which is the date 10 work days
    '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
    '   (just made-up holidays, for example purposes only).
    
    ' Did the caller pass in a date? If not, use
    ' the current date.[/COLOR]
    Dim lngCount As Long
    Dim dtmTemp As Date
    
    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    dtmTemp = dtmDate
    For lngCount = 1 To lngDays
        dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
    Next lngCount
    dhAddWorkDaysA = dtmTemp
End Function

[COLOR=#ff0000][B]Public Function dhNextWorkdayA[/B][/COLOR]( _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant = Empty) As Date
...

 

[Leadparsons]

OK, have copied from top to bottom, now in my query how do input the code ?
I have written [Received]+[dhAddWorkDaysA],
When I run it it asks for a input which I presume is the number of working days I want to add so I put 10, it results in just adding 10 days to my Received date not 10 working days.

[Received] is my original date and [dhAddWorkDaysA] is the module I have created, reading all of the code I presume I only need to refer to this part to add the days but it does not work. I now getting pressured to get this to work or I will have to say it can not be done.

 

[Joe4]

dhAddWorkDaysA is a function, not a field (you would only enclose field names in square brackets).
You use it like you use any other function. It has arguments (parameters) that you need to enter. It is all documented right in the UDF itself.
Take a look at the description and example.

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
   [COLOR=#008000] ' Add the specified number of work days to the
    ' specified date.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' In:
    '   lngDays:
    '       Number of work days to add to the start date.
    '   dtmDate:
    '       date on which to start looking.
    '       Use the current date, if none was specified.
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value, if that's what you want.
    ' Out:
    '   Return Value:
    '       The date of the working day lngDays from the start, taking
    '       into account weekends and holidays.
    ' Example:
    '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
    '   returns #2/25/2000#, which is the date 10 work days
    '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
    '   (just made-up holidays, for example purposes only).[/COLOR]

 

[Leadparsons]

Ive tried just typing the example in and it comes up with a "Data type mismatch in criteria expression" ? I have put

NEW DATE: dhAddWorkDaysA(10,#02/09/2000#,Array(#16/02/2000#,#17/02/2000#)) in the field name of a query.

 

[Joe4]

I tested it out, and it seems to choke on the "Array" part. If I omit that argument, or use a single date, it seems to work fine.

Note. You also should not have spaces in your aliases. Use "NEW_DATE" instead of "NEW DATE". DATE itself is a function in Access, so using it like you did may cause issues also.

 

[Leadparsons]

Ok, I have tried NEW_DATE: dhAddWorkDaysA(10,#02/09/2000#) this now produces a date of 15/09/2000, if it is working out 10 working days from 02/09/2000 how do I get it to work out 10 days from my field called [Received] that has a date that I want to add 10 working days to ?

 

[Joe4]

What you posted is just an example. The first argument is the numbers of days (10). The second argument is the starting date. It doesn't need to be hard-coded, it can be a date field/variable.
So just replace #02/09/2000# with your date field [Received].

 

[Leadparsons]

, thank you very much