By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
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

Narender Sagar
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
  1. 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

✓ answered by NeoPa

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

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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
Dec 1 '11 #2

Narender Sagar
100+
P: 189
Thanks a lot Neopa..
This is what I needed.
It served the purpose.
thanks once again.
kind regards,
Narender Sagar
Dec 2 '11 #3

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome Narender. It always helps when one can understand what the question is saying :-)
Dec 2 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.