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.
17 15376
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
NeoPa 32,556
Expert Mod 16PB
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. - '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
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.
NeoPa 32,556
Expert Mod 16PB
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.
Thank you all for your help and advice. here is what i used and it works well and is easily addapted to some of the crazier forms. i did not build this databse but sorta got excited and took a project that i was not skilled enough take. but i have learned so much.
there is a holiday table that will be populated with the days that we are closed based on the 2009 calendar.
here is what i am using, feel free to comment. any opinion you would like to share would just be more knowledge that i gain as i am still a novice. -
Option Compare Database
-
Option Explicit
-
Public DueDate As String
-
-
Function GetTheDate()
-
-
Dim db As Database, rs As Recordset, sSQL, DW As String
-
Dim loopend, ct, x As Integer
-
'Dim sENum As String
-
-
Set db = CurrentDb
-
DueDate = Trim(Format(DueDate, "m/d/yyyy"))
-
sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
-
-
Set rs = db.OpenRecordset(sSQL)
-
If Not rs.EOF Then
-
DW = rs![HolidayDate]
-
GetTheDate = 1
-
Else
-
GetTheDate = 0
-
End If
-
-
rs.close
-
db.close
-
-
-
End Function
-
-
Private Sub Date_Submitted_LostFocus()
-
Dim db As Database
-
Dim rs As Recordset
-
Dim sSQL, Dept, TN As String
-
Dim vaData As Variant
-
Dim k As Long
-
Dim d, e, x, wct, dct
-
Dim loopend, MyHour, colonPos
-
-
Me.Priority_level_new = "Standard"
-
If Me.Priority_level_new = "Standard" Then
-
Me.Days = "1"
-
End If
-
-
-
-
'if submitted after 4:59 PM then add a day if after 4:59 PM and a Friday add 2 days others wise add no extra days
-
loopend = Val(Me.Days) 'default # of days
-
DueDate = Me.Date_Submitted
-
If Right(Me.Date_Submitted, 2) = "PM" Then
-
colonPos = InStr(Me.Date_Submitted, ":")
-
'If (Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4) And (Trim(Format(Date, "dddd")) = "Friday") Then 'get the day of the week
-
' loopend = loopend + 1
-
If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4 Then
-
loopend = loopend + 1
-
If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) = 12 Then
-
loopend = loopend - 1
-
-
End If
-
End If
-
End If
-
-
'add a day one at a time and check to see if a weekend falls inbetween
-
dct = 0
-
Do
-
d = Trim(Format(DueDate, "dddd")) 'get the day of the week
-
'check for a holiday and a weekend day
-
If d = "Saturday" Or d = "Sunday" Or GetTheDate = 1 Then
-
DueDate = DateAdd("w", 1, DueDate)
-
ElseIf dct = loopend Then 'reached number of business due days
-
Exit Do
-
Else 'weekday
-
dct = dct + 1
-
DueDate = DateAdd("w", 1, DueDate)
-
End If
-
Loop
-
Me.Due_Date = DueDate
-
-
End Sub
NeoPa 32,556
Expert Mod 16PB
Seems like some good progress there Trixx :)
The basic concepts are good and very much in line with the suggestions given.
i had some in house help with the date calculations as well.... dont want to take credit where credit is not due.
NeoPa 32,556
Expert Mod 16PB
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).
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.
-
.... WHERE Day(DateSerial(<d>, <m>, <y>))=<d>
-
- 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.
NeoPa 32,556
Expert Mod 16PB
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.
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.
NeoPa 32,556
Expert Mod 16PB
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.
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 :-)
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?
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
NeoPa 32,556
Expert Mod 16PB @trixxnixon
Probably over half of mine is. Otherwise I do network management and other IT related tasks.
@tdw
HA!!
wallmart
my cell phone can also do it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David Stockwell |
last post by:
I''m wondering if the Calendar object has an option to only do calculations
based on business days (ie M-F). Additionally does it have a way to get
holidays into itself?
Currently I'm...
|
by: Anthony Robinson |
last post by:
Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that...
|
by: igendreau |
last post by:
I have users inputting a "Request Date". Upon entering a date, I need
Access to populate a second field ("Due Date"). When they enter their
Request Date, I want Access to set the default value of...
|
by: CDMAPoster |
last post by:
I'm starting to come up with a version of DateAdd that I call
BusinessDateAdd that adds the selected number of business days. It's
still in preliminary form (needs testing) and interacts with my...
|
by: Sam |
last post by:
Hi,
I use C# in my ASP.NET projects. Here's what I need to do: I want to add x
business days to a given date i.e. add 12 business days to today's date. What
is the best, fastest and most...
|
by: =?Utf-8?B?QWw=?= |
last post by:
I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
|
by: ArchMichael |
last post by:
i need help again on calculating business days excluding holidays
i have a field called assign date and i need to calculate 7 business days excluding holidays ( already have a table for holiday)...
|
by: FishVal |
last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL.
So, let us say the problem is to calculate...
|
by: PotatoChip |
last post by:
I'm working in an Access XP database and I need to create a query which calculates what the date will be 6 business days after . I have no idea where to start and most posts I find on calculating...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |