report functions 1

[acortinas]

I am currently creating a report in Access which needs to contain a column which calculates the difference between two dates while disregarding weekends and holidays. I located a function in Excel (NETWORKDAYS)which does just that, but I have been unsuccessful incorporating it into my report. I am using MS Access 97. Any info. would be helpful.

 

[nthompson]

I just asked a co-worker this same question. She found some functions here

http://www.mvps.org/access/datetime/date0012.htm

that were very helpful. So far, I've been able to make this work on a form and in a query. However, when I try to add the parameter for an array of holidays, I get a datatype mismatch error that I haven't been able to resolve yet. I haven't used the functions on a report yet. Try them and let me know how they work for you.

 

[nthompson]

I also found this in another posting. I haven't tried it yet but it looks simpler...

**********************
This is 'My' holidays table(tblHolidays), refered to in the function below:


HoliDate HoliName


1/1/00 New Year's Day
1/17/00 Martin Luther King Day
2/2/00 Groundhog Day
2/12/00 Lincon's Birthday
2/14/00 Valentine's Day
2/21/00 President's Day
2/22/00 Washington's Birthday
3/8/00 Ash Wednesday
3/17/00 St. Patrick's Day
4/1/00 April Fool's Day
4/20/00 Passover
4/21/00 Good Friday
5/5/00 Cinco de Mayo
5/14/00 Mother's Day
6/11/00 Pentecost
6/18/00 Father's Day
7/4/00 Independence Day
9/4/00 Labor Day
10/31/00 Halloween
11/11/00 Vetran's Day
11/23/00 Thanksgiving
12/25/00 Christmas
12/31/00 New Year's Eve

First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])


Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date&quot

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function