Working days ( DateDiff) Function

[amal1973]

Hello....
This is the Code that i fond to calculate working days ,but there are couple of functions that dont work with access 2000 like the DeltaDays!!? and is dateDiff will do the same thing ? Do i have to have a field for the startdate with a date formate and an Endingdate.?? and where the result should come out? will it be in another field? I thank anyone how will help me start this off.
thank to all...








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

 

[MichaelRed]

You have sevveral question. I will attempt to address some of them.

The original function was written for DAO databases (Access ver '97). If you are using ver 2K, the defaule is for ADO databases. To use DAO, you need to explicitly defing the database and recordset to be of DAO origin / type.

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset


The procedure is a FUNCTION. Functions return results to the "Calling" object. In another PROCEDURE, You would ASSIGN the return value to a VARIABLE, as in:

[tab]Dim MyStartDate as Date
[tab]Dim MyEndDate as Date

[tab]MyStartDate = #3/1/01#
[tab]MyEndDate = #9/15/00#

[tab]MyWorkDays = DeltaDays([i]MyStartDate[/i], [i]MyEndDays[/i])

In a query, it could lok like:

[tab]MyWorkDays: DeltaDays([MyStartDate], [MyEndDays])

Where the fields exist within the query source recordsets.


From your somewhat obviou inexperience, and the ommission of any mention of the holidays table, I feel compeled to remind you to create the (NECESSARY) table and populate it with the dates (and names) of your "Official" holidays. Please read the complete FAQ on the subject.



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[amal1973]

Michael..
i just want to thankl you for your time to explan it to me ...
i will create the table and form and place this function inside a Button..
hope it will work
thanks again
omar