MS Access DATEADD - Syntax error in query-CodePudding

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

CodePudding user response：

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:

DateBase
DateCalc
DateFind
DateWork
VDateWork