Calendar Table

[Thomas Milici]

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

[Mihail]

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 !

[Mihail]

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.