I would like to remove weekends & holiday from an access query

[peter Lu]

Hi all I would like to remove weekend and holidays from my access query: Here is the function i'm using. I tried using a exclude 1&7 for sat and sunday but it doesn't work...

Days Open:DateDiff("d",[Open Date],IIf(IsNull([Closed Date]),Now(),[Closed Date]))

[jpatchak]

You should really use an auxillary calendar table:

http://sqlserver2000.databases.aspfa...dar-table.html

[peter Lu]

thanks, jpatchak! but could I just add to the function i have posted to remove weekends and holidays...

[jpatchak]

I'm not sure there is an easy way to exclude weekends and holidays with a function like that. If you had a simple calendar table with the following fields: Date, IsWeekend, IsHoliday your function would look something like this:

DCount("*", "CalendarTable", "(Date BETWEEN #" & [Open Date] & "# AND #" & NZ([Closed Date], Now()) & "#) AND IsWeekend=False AND IsHoliday=False")

Having a Calendar table will give more flexibility to add/take away holidays, etc... without changing your code. All you'd have to do to add Kwanzaa as an exluded holiday would be to check the IsHoliday field in your calendar table for whatever date Kwanzaa is.

[Stewart Ross]

As jpatchak said, you really need to use an auxiliary table if you want to exclude holidays. There is no automated way of recognising these, as the specific days allocated as holidays vary not just country by country but regionally within many countries.

There are many, many possible variations of holiday dates. If an organisation schedules holiday dates at Easter, for instance, these will vary every year, as the dates for Easter itself are not fixed and can vary widely from one calendar year to another.

Companies can also set local dates for holidays which override other dates in the calendar. The only practical solution to counting or excluding specific dates as holidays is to store those dates in a table set up for that purpose. That in itself does not exclude them - there would be additional logic needed to do so either within an SQL function or a separate user-function, but without the table there is no chance of accommodating actual holiday dates at all.

The link jpatchak posted provides guidance on this topic and outlines comprehensively why and how an auxiliary table is used - I'd strongly advise you to have a look at it!

-Stewart