Solved subtract number of WORK DAYS in report expression (1 Viewer)

[Chief]

Hello,
I have a report that I need to minus certain amount of workdays from a preferred date.
This is what I have that is working, but it is including weekends.
=DateAdd("d",-[LeadTime],[CustomerPreferredDate])

I need it to only use working days. (I'll tackle the whole holiday thing later)

Thank you

 

[theDBguy]

Access doesn't have a workday function like Excel does. You will have to use or create a custom function to do it.

 

[Chief]

Access doesn't have a workday function like Excel does. You will have to use or create a custom function to do it.

Oh Bugga,

Any example for me?
I don't want to store the data, its just a report.

I have a few working date functions in VBA, can I call on one of them in the expression builder maybe?

 

[arnelgp]

create a custom function in a module and use that instead of dateadd():

Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
Dim i As Integer
Dim m As Integer
m = Abs(iDays)
While m > 0
    If iDays < 0 Then
        dteStart = dteStart - 1
    Else
        dteStart = dteStart + 1
    End If
    If InStr(1, "sat/sun", Format$(dteStart, "ddd")) <> 0 Then
    Else
        m = m - 1
    End If
Wend
addDays = dteStart
End Function

=addDays(-[LeadTime], [CustomerPreferedDate])

 

[Chief]

create a custom function in a module and use that instead of dateadd():

Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
Dim i As Integer
Dim m As Integer
m = Abs(iDays)
While m > 0
    If iDays < 0 Then
        dteStart = dteStart - 1
    Else
        dteStart = dteStart + 1
    End If
    If InStr(1, "sat/sun", Format$(dteStart, "ddd")) <> 0 Then
    Else
        m = m - 1
    End If
Wend
addDays = dteStart
End Function

=addDays(-[LeadTime], [CustomerPreferedDate])

Cheers mate,

Worked a treat
Thank you