Exclude weekends and holidays

[Peps]

Hi, I’ve got a DB that amongst other things creates a forwarding list for work orders that have to be submitted 2 working days in advance. The DB in question has a table called “Holidays” that contains two fields: [Holidays] which is a date/time field and [Title] which is a simple text field.

The code below is supposed to exclude weekend days from working days as well as those holidays that are updated in the “Holidays” table. The idea is that the user always gets a forwarding notification list two working days in advance.

Here’s the code:



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
Dim TotalHolidays As String

TotalHolidays = 0

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

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date")

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

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

Case Else 'Normal Workday
strCriteria = "[HoliDays] = " & NumSgn & Format$(MyDate, "dd-mm-yyyy") & NumSgn 'Thanks to "RoyVidar" 2/18/04
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
TotalHolidays = TotalHolidays + 1
End If

End Select

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

Next Idx

DeltaDays = NumDays
Forms!Taxi_Service!TrueHoliday = TotalHolidays

End Function


I’ve got a date field on a from that executes the above “After Update”


Here’s the AfterUpdate code:

Private Sub Itinerario_Fecha_1_AfterUpdate()

Dim Calculate_Days As String
Dim SMyDate, SMyWeekDay

If Update = True Then
Else
Creado = Forms!Active_Information!UserId & " / " & Forms!Active_Information!Text49 & " - " & Now()
End If

Calculate_Days = DLookup("Días", "Setup")
Days = Itinerario_Fecha_1 - Calculate_Days

Dim HOLMynum As String
Dim HOLPosition As String
Dim StopDate
HOLPosition = 0
HOLMynum = 21
Do Until HOLMynum = 0

MyDate = Days
MyWeekDay = WeekDay(MyDate)
NoDate = MyWeekDay
SMyDate = Itinerario_Fecha_1
SMyWeekDay = WeekDay(SMyDate)
SNoDate = SMyWeekDay
Delta = DeltaDaysVisa([Days], [Itinerario_Fecha_1])
Days = Itinerario_Fecha_1 - Calculate_Days - TrueHoliday
HOLMynum = HOLMynum - 1
HOLPosition = HOLPosition + 1


If StopDate = 1 Then HOLPosition = 21
If HOLPosition = 21 Then Exit Do

Loop

[Fecha de Impresión] = Days

DoCmd.RunCommand acCmdSaveRecord
End Sub


Note: Calculate days = 2


Problem: While the weekend days are always excluded, the code does not always recognize the days form the “Holidays” table.


I hope that the above explanation is clear enough for someone out there who can give me a hand.

Thanks,

 

[RuralGuy]

Access (Jet) really wants date in the US format.

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

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[MichaelRed]

it is seldom necessary to post the code for a function taken directly from the (Tek-Tips) site, just reference the thread / faq in your inquiry with the particulars of your problem . issue.

While RuralGuy was astute in picking up trhe (probable) issue of the date format not being (U.S.) format.




MichaelRed

 

[Peps]

Thanks for the feedback, I've tried using the US date format but without any luck. I'm gonna have to go through the code again. To be honest I'm not 100% sure why the us date format makes any difference, I've got a few DB's with date queries and codes that work perfectly and the date format is not US. I will take this into consideration for future problems anyway.

Micheal, as you can see, the original Tek-Tips code has been slightly modified. Please correct me if I'm wrong, but I'm sure that the best answers/solutions are only available when all relevant information and codes are correctly posted.


Thanks,

 

[MichaelRed]

oops ... my bad ... didn't check all that closely after recognizing the function name and top few lines.

but then, reviewing the changes do not observe any that would bear on the holidayness of a given date.

the changes I do observe are not in concert with the original design intent and are (at least to me) a poor approach, as they require a specific named form to be open to prevent an error. If this is NECESSARY it would imply that the function should be made a private sub and might as well directly incorporate the other routine and just feed info to the form controls.

The issure of the date format is dependent on the format of the arguments to the DeltaDays procedure. Ms. Access (and many of the office suite programs) is -internally- the same and NOT in anything like the traditional m-d-y (or y-m-d) structures, but is a simple 'floating pooint' number which is interperted according to the date format of the settings.

There are numerous threads in these )Tek-Tips) fora discussing the internal storage of dates and the (U.S. at least) interpertation / translation of that information to the more conventional presentation.

The entry of date type data into tables is goverened by the settings, so that U.S. date of #8/21/1942# and Eur date 0f #1942/8/21# result in the same internal storage, then, retrieval of the date (e.g. in a query) must also respect the settings. While attempting to NOT get into a lenghty discussion of the additional details, I would suggest a review of the data data format (mentioned above) and -by (at least temporairly) removing your changes to the function, try some manual input calls with the different date formats, comparing the results to your expectations and manual calculations.




MichaelRed