424,852 Members | 994 Online
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

 P: 98 i have a form with these fields Priority level: urgent critical standard business days: 1, 3, 15 date submitted: current date due date: the due date will calculate based on how many business days are returned which is based on priority level, for example 15 days passed 10/22/08 would return a date of 11/12/08. i have no idea what i am doing, if anyone can help i would greatly appreciate it. i would do almost anything to get this done asap. Thank you in advance. Oct 23 '08 #1
17 Replies

 P: 93 Hi If you have business days already on the form then just add a text box control and type =DateAdd("d",[YourDateControlName],[YourBusinessDaysControl]) Hope it helps (and makes sense) Regards Emil Oct 23 '08 #2

 Expert Mod 15k+ P: 31,310 Business days are tricky (trixxie). Weekdays are algorithmic, but business days must, by definition, be based on known data. DateAdd("w", 5,Date()) should give the date one week hence. Unfortunately, at least up to version 2003, this simply adds days. If you're interested, I have a routine to handle this though. Be sure to understand this only deals in week days, NOT business days. Expand|Select|Wrap|Line Numbers 'MoveWD moves datThis on by the intInc weekdays. Public Function MoveWD(datThis As Date, _                        intInc As Integer) As Date     MoveWD = datThis     For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)         MoveWD = MoveWD + Sgn(intInc)         Do While (WeekDay(MoveWD) Mod 7) < 2             MoveWD = MoveWD + Sgn(intInc)         Loop     Next intInc End Function Oct 23 '08 #3

 P: 69 I endorse what NeoPa says: there really is no practical alternative to creating a table of days with a flag set appropriately for each day. Another factor that you need to bear in mind is that holidays are not universal. Most are country specific and some are even region specific within a country. Another thought: if you ever need to deal in daily working hours which vary according to the day of the week, or accounting periods which are not month based, you really have no choice but to go the route I suggest, with extra fields on the day table to manage all the other complications. This approach may seem a lot of work, but my experience is that in the long run it's really the only method that works. Oct 24 '08 #4

 Expert Mod 15k+ P: 31,310 An alternative is to use a table of holidays rather than a table of all dates within a range. A complication with any way of doing this of course, is that it's quite impractical to populate this table too far into the future (as many holidays are not entirely predictable). This approach must involve regular (if infrequent) maintenance of the data. If this sounds like something you're prepared to do, then go for it. Otherwise, try your hardest to get acceptance for a simple weekday based procedure. Oct 24 '08 #5

 Expert Mod 15k+ P: 31,310 Seems like some good progress there Trixx :) The basic concepts are good and very much in line with the suggestions given. Nov 25 '08 #7

 P: 98 i had some in house help with the date calculations as well.... dont want to take credit where credit is not due. Nov 25 '08 #8

 Expert Mod 15k+ P: 31,310 Fair enough. But sometimes it's about who has the brights to ask for/get help. Anyway, it's all progress, so allow yourself some congratulations (while thanking those people who helped). Nov 25 '08 #9

 Expert 2.5K+ P: 2,653 Hello, gentlemen. Just to make everyone happy I would like to say that the problem has a pure SQL solution. The main features of the solution are the folowing: Sequential dates list is being generated dynamically via cartesian join of 3 tables containig days (1 to 31), months (1 to 12) and years (list should cover expected range). Certain 31, 30 and 29 days are excluded using comparisson of DateSerial() result with day argument. Expand|Select|Wrap|Line Numbers .... WHERE Day(DateSerial())=   Periods to calculate business dates from associated with contact are stored in table. Different contacts are associated with different sets of days off. Different contacts could be associated with different sets of holidays. Holidays having the same d/m each year could be stored as single record. Flat dates list obtained via cartesian join is outer joined with periods date to filter dates falling within periods, then with days off associated with particular contacts to remove days off records, then with holidays. Then aggregating query and, voila, business days count calculated. Regards, Fish P.S. When HowTo section will be alive I will post an article there including db sample. Nov 25 '08 #10

 Expert Mod 15k+ P: 31,310 An interesting concept Fish. I doubt too many of our ordinary members will want to get to grips with these levels of complexity, but options are always worth looking at (and I can think of a few of the staff who may be very interested in looking further into this). When you have the article ready post a link in here so that we can have a look. A couple of thoughts spring to mind, but I will go there when you're more ready with it, and elsewhere than in this thread. Nov 26 '08 #11

 100+ P: 206 Considering that I have an alarm clock from Walmart that knows to only go off on workday mornings and not weekends, it seems strange to me that Microsoft doesn't just incorporate something into the program that can do the same. Can't Outlook do this? I wonder if there would be a way to borrow Outlook's ability for this and use it in Access. Nov 26 '08 #12

 Expert Mod 15k+ P: 31,310 If you look earlier on in the thread John you'll notice that weekdays and weekends are fairly straightforward to include. What is more complex, is the complications of holidays of various sorts. Some fall on the same day of the same month every year. Some fall on the last Monday of a month. Some fall on days that are only predictable by the leaders of your country. Some fall on days nominated by a random number generator (Ok - a little hyperbolae there :D). In short, though some fit into an algorithmic approach, some certainly do not seem to. It is possible, with successively more complicated procedures, to cover more or less of these dates. Fundamentally though, as so many of them are hard to tie down at all, reliably predicting them in any way within a database will always be at least complicated, assuming it is even possible. Nov 26 '08 #13

 100+ P: 206 Weather is even less predictable, and yet we can get the weather streamed in to our systrays. Microsoft really should have a similar set up for holidays, where the information is kept in sync via the internet, and able to be accessed or called from within Office programs. ...Or at least be able to manually flag holidays in the Outlook calendar and let Access read from that. Sorry, I'm just thinking out loud (actually, silently...but with my fingers on the keyboard). Not particularly helpful in answering the poster's question. But it looked to me like the thread was about over anyway, until FishVal had an article ready :-) Nov 26 '08 #14

 P: 98 you guys are all awesome, this is by far my favorite forum to post and search in. do you guys all have jobs that are access or database related? Nov 26 '08 #15

 Expert 2.5K+ P: 2,653 Gentlemen, I've posted the article on Access forum. HowTos are still down. Moreover, I couldn't attach sample db - maximum attachment size is set to 5k. :( Regards, Fish Nov 27 '08 #16

 Expert Mod 15k+ P: 31,310 @trixxnixon Probably over half of mine is. Otherwise I do network management and other IT related tasks. Nov 27 '08 #17

 P: 98 @tdw HA!! wallmart my cell phone can also do it. Dec 1 '08 #18