Datediff and working days - how?

[joolsUK0575]

Hi there!

I need to run some stats for my workplace that involves working out if applications have been registered within 3 days.

How in the heck is this done

I've had a browse through the forum and I noticed that VB seems to come in to it.

Do I really have to use VB to work it out and if so how

At the moment I am using this which just works out the days - Within 3 Days: DateDiff("d",[dbo_table1]![date_valid],[dbo_table1]![DateKeyed])

Thanks for any help. This is one I would most definitely like to sort out

Jools

 

[The_Doc_Man]

Unfortunately, this is not a trivial problem. However, this question has appeared before on this very forum. Try searching for "Working Days" as a topic. We might have a few posts that would help.

Do I really have to use VB to work it out

Unless you want to be really ugly about it, 'fraid so. Particularly if the difference is more than just a few days, you run into the issue of holidays that aren't also weekends.

But, you asked the question and I can answer it.

There IS a way to do this that doesn't involve VBA, actually, but it is a pain in the toches. Create a table that contains one record for every day. (yes, 365 entries per year). You will probably need several years worth of days, forwards AND backwards. You have to decide how many days to track. You might have to update it from time to time.

This table has two, three, or four fields depending on how you want to deal with it. BE WARNED: This solution is not a "purist's" solution. It is in response to the "how do I do this without VBA?" question. Among other things, it violates several good database practices. But it requires no VBA.

tblRefDates
TheDate: Date, PK
IsHoliday: Yes/No
IsWeekend: Yes/No
IsWorkday: Yes/No

Visit each holiday by hand to mark it YES. Now write an update query that visits each record to update the Isxxxx fields based on a couple of computations. The rule would be that you can compute whether it is a Saturday or Sunday using the DatePart function to look at the weekday. So with the query, go through and mark each record YES if it is a weekend.

Now one last query is required to mark the days that are neither weekends nor holidays. These must be workdays. Mark them as the result of the expression NOT ( IsWeekend OR IsHoliday ).

Having done this, you can now write a query that counts workdays between two given dates. Count the number of days for which IsWorkday = YES. Look up the "Between ... And ..." operator to select the starting and ending dates for your query.

Would I do this? Probably not. I'd do the VBA. BUT - you asked if there was a way to do this without much VBA. Here's your answer. Ugly, ain't it?

 

[joolsUK0575]

OK. If I have to go the VB route, how do I do it?

I have never ever done a query that uses VB and would not have a clue as to where to start

Thank you for the help so far

Jools

 

[joolsUK0575]

Since my post I came across this link (http://www.mvps.org/access/resources/downloads.htm ) which has helped with my working day/ weekend problems in my query.

Slight problem!

I kept in my original Datediff (see earlier post) just to compare the results of the datediff against the results produced by the module.

On some of the results the datediff says "3" but the module says "4". Yikes

Any ideas

Thanks

Jools

 

[Mile-O]

If you do a search on my name and holidays you'll probably find a bunch of posts where I eliminate weekends and holidays (Easter, Xmas, Bank Holidays) from a date diff function.