Hello,
I am looking for a way to add a number to a specified date. When added it will predict a future date that would have excluded weekends. I found this function on the web but I am not sure how to use it or if it will work with what I am working on.
I added the below function to the module and In my query I call the function. My query looks like:
[blue]
ROUGHGRADE: dhAddWorkDaysA([JP116AverageTable]![AvgOfCTC-RG],[JPFA182NewContractsNOinvoicesTablePart1].[CTCDTE])[/blue]
CTCDTE is a date field
AvgOfCTC-RG is a number field
When I run the query I get "Undefined Function 'dhAddWorkDaysA' in expression" Am I approaching this correctly or might there be an easier way of doing this?
' ********* 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.
'
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' 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.
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
I am looking for a way to add a number to a specified date. When added it will predict a future date that would have excluded weekends. I found this function on the web but I am not sure how to use it or if it will work with what I am working on.
I added the below function to the module and In my query I call the function. My query looks like:
[blue]
ROUGHGRADE: dhAddWorkDaysA([JP116AverageTable]![AvgOfCTC-RG],[JPFA182NewContractsNOinvoicesTablePart1].[CTCDTE])[/blue]
CTCDTE is a date field
AvgOfCTC-RG is a number field
When I run the query I get "Undefined Function 'dhAddWorkDaysA' in expression" Am I approaching this correctly or might there be an easier way of doing this?
' ********* 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.
'
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' 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.
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
