473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Narender Sagar
189 100+
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

3 6969
NeoPa
32,556 Expert Mod 16PB
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
189 100+
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
32,556 Expert Mod 16PB
You're welcome Narender. It always helps when one can understand what the question is saying :-)
Dec 2 '11 #4

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

Similar topics

6
by: Dimitri Tholen | last post by:
Hi all, For a project I m working on I am looking for a database with all current car-makes & models from post-WII till now. With all the car websites around I am sure this information should...
5
by: Maarten | last post by:
Hi, i need to fill a dropdownlist in a frame with only saturdays. The code must be like: <select class="fld" size="1" name="arrivaldate"> <option value="17/01/2004">17/01/2004</option>...
2
by: ROSY | last post by:
hello , what's the basic difference between #define & const. plz reply. thanks in advence.
13
by: uma676 | last post by:
Hi all, I want to know the differebces between function and macro in c language. if anybody can tell me atleast 4 diff's. for which i will be regrated. Rgds, Balaji
5
by: rjfjohnson | last post by:
Hey, Today is Thursday 16-Feb-06. The same thursday last year is 17-Feb-05. Because I am comparing daily sales between years, I need to know the date of the same weekdayname as last year, so...
4
nirmalsingh
by: nirmalsingh | last post by:
hi all! i have to find the dates of sundays in a year. help me with sample code in C#. thanx in advance.
0
by: Steven Prasil | last post by:
When I start my VisualStudio 2005 it creates automatically new folders: D:\work\Visual Studio 2005 D:\work\Visual Studio 2005\Projects D:\work\Visual Studio 2005\Templates Yes, in VS menu ...
2
by: crabb | last post by:
I would like to know if anyone knows how to set up an app that would count the number of specific days in a user defined year. example, how many Sundays, Mondays, Tuesdays, etc. in 2007 or a user...
18
by: jmarcrum | last post by:
Hi everyone! I have a form that when the user opens it, the Date combo box on the form is populated with every month of the year. But I don't want it to do that! I want it to populate with the...
14
by: Tommy Jakobsen | last post by:
Hi. Is there a method in .NET that takes "year" as an argument and returns the total number of weeks in that year? For culture da-DK (Danish). Thanks in advance. Tommy.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.