By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Calendar Table

P: 1
Hello, I have a query for suspending students. I want to take the suspension day, add the days suspended (-1 to include the first day of suspension)then exclude weekends and holidays. I already have a calendar table in my database. I am currently using the following function
Expr1: DateAdd("d",Suspension_Tracker_1A!Behavior_Num_Of_ Days-1,Suspension_Tracker_1A!Behavior_Date_Report)to count the first day of suspension and end up with the last day of suspension, however I now need to add the omission of holidays and weekends. I am looking at this function as part of the solution
DCount(“*”,”Table_Calendar”,”(Date BETWEEN #” &[Open Date] & “# AND #” &NZ ([Closed Date], Now()) & “#) AND Is_Weekend=False AND Is_Holiday=False”) I pulled this off of the net so the fields are obvioulsy incorrect and I am thinking I need to morph these two together to get what I need. Can anyone help? Thank You
Feb 25 '12 #1
Share this Question
Share on Google+
2 Replies

P: 759
An idea:
Assuming that your calendar table contain a field Day_Date (this field must exist because the table is a calendar) and a field named IsHolyday (Yes/No type of data) design a query based on this table using this conditions:

Under the field Day_Date:
- first row in Criteria: >= FirstDayOfSuspension
- second row in Criteria: <= LastDayOfSuspension (calculate with DateAdd() function)
Under field IsHolyday:
- Yes

Count the records for this query and subtract the result from total days of suspension.

Maybe other one can sketch the SQL for that.
Sorry, but based on my skills, I can't.

Good luck !
Feb 25 '12 #2

P: 759
I think that is a little mistake in my first post:
You must ADD, not subtract, this days.
But, by adding this days, new holidays can occur.
So you must iterate the calculation for number of days until no more holidays occur.
Feb 25 '12 #3

Post your reply

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