435,264 Members | 1,056 Online
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

# Identify Second and Fourth Saturdays of Each Month, and All Sundays, for a Whole Year

 100+ P: 189 Dear All, I want to create a database, in which I have to maintain Production Plan for the year. The condition is that every 2nd and 4th Saturday & all the Sundays in a month will be Holiday. There can be more holidays in a year for which there will not or should not be any production plan. Can you please guide me, what should be my table structure? I have created following tables: 1) tblDays 2)tblMonths 3)tblYears 4)tblHolidays. And by using functions Expand|Select|Wrap|Line Numbers dteDate: DateSerial(tblYears.Year,tblMonths.Month,tblDays.Day) ) in the query, I am able to generate Dates for the entire year. Now I want to indicate non working days wrt Saturdays, Sundays and other Holidays, in other query, for which I am not able to figure out for alternate Saturdays. thanks regards Narender Sagar Dec 1 '11 #1

I would produce a Public function procedure which is passed a date and returns a boolean result to indicate whether or not the date matches the specified criteria.

Separately, I would maintain a table of holiday dates which would need to be maintained.

A date would then be considered a holiday if either :
1. It matched a date in the holiday table.
2. The function returned the value True.

The function would be similar to :
Expand|Select|Wrap|Line Numbers
1. Public Function IsHol(datThis As Date) As Boolean
2.     IsHol = True
3.     If Weekday(datThis, vbSunday) = 1 Then Exit Function
4.     If Weekday(datThis, vbSaturday) = 1 Then
5.         If Day(datThis) > 7 And Day(datThis) < 15 Then Exit Function
6.         If Day(datThis) > 21 And Day(datThis) < 29 Then Exit Function
7.     End If
8.     IsHol = False
9. End Function