MS Access Question

Factual Questions
1

I can’t figure out how to accomplish this, so am asking here in hopes that one of you masters can help me.

I’m trying to figure out how to get Access to discount weekends and holidays when counting days.

Here’s the situation. A consults comes in for review and referral. It comes in on late Friday afternoon, say 4:30 pm on 5 Jun. The consult, of course, won’t be reviewed until Monday morning at the earliest, which would be less than 24 working hours.

BUT…Access counts the weekend days, so now our providers have busted the standard.

The fields in question would be sort of like this:

Referral date: 5 June 09
Review Date: 8 June 09
Consult_Review_lead_time: 3 days (this of course is a calculated field based on the previous fields)

I hope I’m making myself clear.

Any suggestions?

2

It’s been a long time since I’ve done this, but here’s the general idea.

I’m assuming you’d never use a weekend or holiday date for either entered date (you’d want to code restrictions into your form to be sure).

Calculate the total days between with DateDiff.

For weekends, if it’s never more than a week between referral and review (again, code requirements into your form), then the easiest is just to compare the Weekday formats for the two dates. (Weekday format numbers each day as a day of the week; default is 1 for Sun thru 7 for Sat.) If startdate weekday > enddate weekday, then you’ve had a weekend in between, so delete 2 from your total. In your example, startdate is Friday = 6 and enddate is Monday = 2. Since 6 > 2, you know it crossed a weekend. The only problem is times more than 6 days apart (e.g., Friday and Friday) because you start repeating the weekday value.

If that won’t work with your requirements, you have to count the number of Saturdays in between and Sundays in between and then subtract those numbers from your total. It’s something to do with counting the weeks, but I’d have to work a lot harder to remember how that’s done, so I didn’t bother. If you need that, say so. :wink:

For holidays, you’ll need to set up a table and list the holiday dates. (A lot of them could be calculated, but I’d bet you’d spend more time figuring out how to do that then you’d spend typing the next 10 years of holidays into a table.)

Then you just query for the count of holiday dates in between your dates and subtract that from your total.

Make sense?

OTOH, if you’re actually calculating working hours, that’s something else again.

You might try searching Access Help and online for this. It’s been done many times, so you could probably snag some code.

3

Thanks. I got wrapped up in another hot issue and only just got back to this.

I’m one of those “fiddlers”. I have to play to figure things out and am by no means a programmer. I can create simple databases and queries.

I’ll have to take some to play and I’ll also look at MS Access on line. My former boss, who created this particular database, and is a whiz at this sort of stuff, couldn’t figure it out. I was trying to simplify things for me and my co-worker, but I trhink I just made things harder.:slight_smile:

4

Bigresouce.com was very useful to me with stuff like this when I was learning/developing an Access Database last year, though this isn’t something I tried to do myself. I’m sure one of these threads could point you in the right direction.

This messageboard was also very helpful.
Thread 1
Thread 2

Good luck! I kind of miss doing this stuff - like you, I was a total newb, but became a decent Google-programmer!

5

Excel has a function called NETWORKDAYS.

It can be used in Access with a little bit of work. Link

6

Thanks, I’ll look at those sources too.