>need assistance on my function

[nilses]

Hello,

I need assistance on my function. I try to find the number of bank holiday between two dates and I wish to take into account saturdays and Sunday or only Sunday. What is strange in my function it is that I return only from the zero. I have the impression that it does not arrive at reading information in my table. Could you say to me why. Or I have make an error

Function CompteJoursFeriés(dhDébut As Date, dhFin As Date) As Integer

Dim rstNew As Recordset
Dim db As Database
Dim strFiltre As String
Dim strAncienFiltre As String
Dim intEnregistrements As Integer
Dim strSQL As String
Dim strDateFérié As Variant
On Error GoTo TraitementErreur

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblJoursFériés", dbOpenDynaset)

strDateFérié = rst("DateJourFérié")

If Not rst Is Nothing Then
If Len(strDateFérié) > 0 Then
If Left(strDateFérié, 1) <> "[" Then
strDateFérié = "[" & strDateFérié & "]"
End If
strFiltre = strDateFérié & " BETWEEN #" & dhDébut & "# AND #" & dhFin & "#"
strAncienFiltre = rst.Filter
rst.Filter = strFiltre
Set rstNew = rst.OpenRecordset()
If rstNew.RecordCount > 0 Then
rstNew.MoveLast
intEnregistrements = rstNew.RecordCount
End If
' Pour ne pas compter à double un jour férié tombant sur un weekend
rstNew.MoveFirst
Do While Not rstNew.EOF
If EstWeekend(rstNew!DateJourFérié) Then
intEnregistrements = intEnregistrements - 1
End If
rstNew.MoveNext
Loop
rstNew.Close
End If
End If
TraitementFinal:
CompteJoursFeriés = intEnregistrements
Exit Function
TraitementErreur:
Resume TraitementFinal
End Function

Function EstWeekend(dhTemp As Date) As Boolean

Select Case Weekday(dhTemp)
Case vbSaturday, vbSunday
EstWeekend = True
Case Else
EstWeekend = False
End Select
End Function



Thank you for your assistance

Nilses

 

[Mile-O]

Pouvez-vous expliquer votre problème en plus détail en français?

 

[nilses]

Merci Mile-O-Phile

Mon problème et qu'il n'affiche que des zéros comme résultat alors que j'ai un exemple ou j'ai inséré entre deux dates un jour férié. J'ai le sentiment qu'il n'arrive pas à se connecter à la table et à lire le champ. Ma table est bien tblJoursFériés et le nom du champ de ma table est bien DateJourFérié. Comment faire pour lire ces valeurs et les comparer?.

est ce que cette explication est complète?.

Merci par avance

Nilses

 

[Mile-O]

I felt that the translation to English was missing details:

Nilses's problem is that his function only returns zeros as a result whereas he has tried an example where a public holiday is selected/appears between a range of two dates so his function should return a value of 1.

He has a feeling that it is not able to connect to the table and read the field.

The table is tblJoursFériés and the name of the field in the table is DateJourFérié.

How do you make this read these values and compare them?

 

[Mile-O]

strDateFérié = rst.Fields("DateJourFérié")

 

[raskew]

Not sure I understand the scenario entirely.

If tblJoursFériés is a table of holidays looking something like this:

theDate	   Holiday	        Remarks
1/1/03	   New Year's Day       January 1st
1/20/03	   M.L. King Day	Third Monday in January
2/17/03	   Presidents Day	Third Monday in February
3/5/03	   Ash Wednesday	46 days before Easter
3/17/03	   St. Patrick's Day	March 17th
4/18/03	   Good Friday	        Friday before Easter
4/20/03	   Easter	        Varies
5/11/03	   Mothers Day	        Second Sunday in May
5/26/03	   Memorial Day	        Last Monday In May
5/29/03	   Ascension Day	39 days after Easter
6/8/03	   Pentecost	        49 days after Easter
6/15/03	   Fathers Day	        Third Sunday in June
7/4/03	   Independence Day	July 4th
9/1/03	   Labor Day	        First Monday in September
10/13/03   Columbus Day	        Second Monday in October
10/31/03   Halloween	        October 31st
11/11/03   Veterans Day	        November 11th
11/27/03   Thanksgiving Day	Forth Thursday in November
12/25/03   Christmas Day	December 25th
12/31/03   New Year's Eve	December 31st

…and the intent is to provide a startdate and enddate and determine if any of the holidays fall within those parameters, excluding those that fall on specified days (e.g., Saturday and/or Sunday), you might consider the following.

Public Function CompteJoursFeriés(pStartDte As Date, pEndDate As Date, pExclude As String) As Integer
'*******************************************
'Name:      CompteJoursFeriés (Function)
'Purpose:   Returns the number of holidays
'           which fall between two specified
'           dates and which do not fall on
'           on specified weekdays
'Author:    Bob Askew (raskew)
'Inputs:    ? CompteJoursFeriés(#4/1/03#, #9/15/03#, "17")
'           This is asking for the number of holidays that
'           fall between 1-Apr-03 and 15-Sep-03 which don't
'           occur on a Sunday (1) or a Saturday (7)
'Output:    5 (using my tblHolidates)
'*******************************************

Dim db As DATABASE
Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
 
Set db = CurrentDb

'tblHolidates includes dates only for the current year, [theDate] = the holidate
strSQL = "SELECT tblHolidates.theDate, tblHolidates.Holiday, Weekday([theDate])" _
    & " AS TheWeekDay FROM tblHolidates" _
    & " WHERE (((theDate) Between #" & pStartDte & "# And #" & pEndDate & "#)) AND" _
    & " ((InStr('" & pExclude & "', Weekday([theDate])))=0);"

Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Close
db.Close
Set db = Nothing
CompteJoursFeriés = n

End Function

You can view the query operation (changing table and field names as necessary) with this query SQL.

SELECT tblHolidates.theDate, tblHolidates.Holiday, Weekday([theDate]) AS TheWeekDay, Format(Weekday([theDate]),"dddd") AS aufEnglish
FROM tblHolidates
WHERE (((tblHolidates.theDate) Between [enter start date] And [enter end date]) AND ((InStr([numbers to exclude],Weekday([theDate])))=0));

I have a small sample application that I could e-mail if you desire. If so, please provide your e-mail address.