I am trying to use the dateadd function in Access 2007.
I am getting a compile error - "There was an error compiling this function. The Visual Basic module contains a syntax error.
DATEADD("w",2,[MyDateField])
I am not trying to use any VBA just adding 2 weekdays to a field in a query - Any help would be appreciated
DateAdd can only add days, not workdays. For that, a custom function is needed.
In a query, you can use my function, VDateAddWorkdays:
Select *, VDateAddWorkdays(2, [MyDateField]) As Date2
From YourTable
The function:
' Adds Number of full workdays to Date1 and returns the found date.
' Number can be positive, zero, or negative.
' Optionally, if WorkOnHolidays is True, holidays are counted as workdays.
' Returns Null if any parameter is invalid.
'
' For excessive parameters that would return dates outside the range
' of Date, either 100-01-01 or 9999-12-31 is returned.
'
' Will add 500 workdays in about 0.01 second.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-19. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function VDateAddWorkdays( _
ByVal Number As Variant, _
ByVal Date1 As Variant, _
Optional ByVal WorkOnHolidays As Boolean) _
As Variant
Dim ResultDate As Variant
ResultDate = Null
If IsDateExt(Date1) Then
If IsNumeric(Number) Then
On Error Resume Next
ResultDate = DateAddWorkdays(CDbl(Number), CDate(Date1), WorkOnHolidays)
On Error GoTo 0
End If
End If
VDateAddWorkdays = ResultDate
End Function
As you can see, it takes advantage of some helper functions and a table holding holidays if those are to be taken into account as well.
Too much code to post here - the functions can be found in my project at GitHub: VBA.Date.
The specific modules needed will be: