If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Adding Business Days only
The following has been working for me for about 10 years:
http://www.datastrat.com/Code/GetBusinessDay.txt -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "TotallyConfused" wrote in message ... Can someone please help how to revise the following by adding only 7 "Business Days". The following is in my form field date on Click Event: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) Thank you in advance for any help you can provide. |
#12
|
|||
|
|||
Adding Business Days only
Thank you for your response. It seems your function looks at a Holiday
table. What would this table consist of? Just Dates? I am sorry for the questions but I new to code and am trying to understand and apply. Sorry if I sound anal but I am trying to incorporate something simple to apply to what I already have. I don't have much time in fixing this. I have two instances where I need to add business days. The first is a form. The second is a report. How do I incorporate your function. I know to save it to Module, how do I call it from either of my samples below. Thank you in advance for your help. The following is in my subform field 1stNotice, Event Click: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) "Arvin Meyer [MVP]" wrote: The following has been working for me for about 10 years: http://www.datastrat.com/Code/GetBusinessDay.txt -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "TotallyConfused" wrote in message ... Can someone please help how to revise the following by adding only 7 "Business Days". The following is in my form field date on Click Event: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) Thank you in advance for any help you can provide. |
#13
|
|||
|
|||
Adding Business Days only
Thank you for your response. Does your function look at a table? Some of
the responses I have gotten look at tables for their function. I rather not have to look at a date table. Do not have any idea how to go about. I am sorry for the questions but I new to code and am trying to understand and apply. Sorry if I sound anal but I am trying to incorporate something simple to apply to what I already have. I don't have much time in fixing this. I have two instances where I need to add business days. The first is a form. The second is a report. How do I incorporate your function. I know to save it to Module, how do I call it from either of my samples below. Thank you in advance for your help. The following is in my subform field 1stNotice, Event Click: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) "raskew via AccessMonster.com" wrote: Hi - The following will add or subtract business days from a given day. Copy/paste to a standard module and call the function as shown: Function UpBusDays3(pstart As Date, _ pNum As Integer, _ Optional pAdd As Boolean = True) As Date '******************************************* 'Purpose: Add or subtract business days ' from a date 'Coded by: raskew 'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True) ' 2) -) ? UpBusDays3(#2/22/06#, 3, False) 'Output: 1) +) 2/22/06 ' 2) -) 2/17/06 '******************************************* Dim DteHold As Date Dim i As Integer Dim n As Integer DteHold = pstart n = pNum For i = 1 To n If pAdd Then 'add days DteHold = DteHold + IIf(WeekDay(DteHold) 5, 9 - WeekDay(DteHold), 1) Else 'subtract days DteHold = DteHold - IIf(WeekDay(DteHold) 3, Choose(WeekDay (DteHold), 2, 3), 1) End If Next i UpBusDays3 = DteHold End Function HTH - Bob TotallyConfused wrote: Thank you However I found this in my search for something very simple. Need help reading this. This was in answer to a very similar question on this Discussion Group. The user was asking for a formula and he was given this formula. He responded that it worked. What is dtm and where would this formula be entered? Any help will be greatly appreciated. Thank you. dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6, DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDat e)) Thanks for your response. However, I don't have much time to spend on this. As ususal this is asap. I thinking how about if I modify this by using Long [quoted text clipped - 27 lines] Tweak to fit your actual business rule which I don't quite understand given your examples. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200803/1 |
#14
|
|||
|
|||
Adding Business Days only
On Sat, 15 Mar 2008 12:50:01 -0700, TotallyConfused
wrote: Thank you However I found this in my search for something very simple. Need help reading this. This was in answer to a very similar question on this Discussion Group. The user was asking for a formula and he was given this formula. He responded that it worked. What is dtm and where would this formula be entered? Any help will be greatly appreciated. Thank you. dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6, DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDat e)) dtm is just part of the naming convention that the person who posted this chose to use. Access does not interpret the dtm at all - it could be xyzNextWeeklyDay or anything else. It's JUST A NAME for a VBA variable. I presume that this line of code was included in something else, but since you chose not to post the rest of the code nor to answer my questions (repeated below) I'm at a loss to help further. Now I don't understand. You want to add seven business days unless it's Friday, in which case you want to add 6??? You had two examples, adding "w", 7 and "w", 3. Confusingly, the "w" dateadd operand works exactly the same as "d" - it just adds days. And here's my suggestion which will answer your question - IF I'm interpreting your question correctly, which you have not clarified. Did you perchance try this? If you want to add 7 weekdays, I calculate that you need to add 9 days if you're starting Monday through Wednesday (skipping one weekend) and 11 days if it's Thursday or Friday; so you could use Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) 5, 9, 11), [1stNotice]) -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Adding Business Days only
Thank you John for responding. I am sorry if I have confused you and not
explained completely. My first example is from a form where user enter date in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from 1stNotice date. This is an event in my form for 1stNotice field. Private Sub 1stNotice_Click() Me.IstNotice = Date Me.Noticedue = DateAdd("w",7,[1stNotice]) End Sub My second example is for separate Report where I have similar issue. But it is not in an event it is in the control source for textboxes. In this report, I have one text box that enter Now(). In my second text box I add 3 days to the Now(). However, I need workdays/business days only. So if it is Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday. The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) Hope this makes better sense. I hope that you will still be able to help. Thank you very much for your time and patience. "John W. Vinson" wrote: On Sat, 15 Mar 2008 12:50:01 -0700, TotallyConfused wrote: Thank you However I found this in my search for something very simple. Need help reading this. This was in answer to a very similar question on this Discussion Group. The user was asking for a formula and he was given this formula. He responded that it worked. What is dtm and where would this formula be entered? Any help will be greatly appreciated. Thank you. dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6, DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDat e)) dtm is just part of the naming convention that the person who posted this chose to use. Access does not interpret the dtm at all - it could be xyzNextWeeklyDay or anything else. It's JUST A NAME for a VBA variable. I presume that this line of code was included in something else, but since you chose not to post the rest of the code nor to answer my questions (repeated below) I'm at a loss to help further. Now I don't understand. You want to add seven business days unless it's Friday, in which case you want to add 6??? You had two examples, adding "w", 7 and "w", 3. Confusingly, the "w" dateadd operand works exactly the same as "d" - it just adds days. And here's my suggestion which will answer your question - IF I'm interpreting your question correctly, which you have not clarified. Did you perchance try this? If you want to add 7 weekdays, I calculate that you need to add 9 days if you're starting Monday through Wednesday (skipping one weekend) and 11 days if it's Thursday or Friday; so you could use Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) 5, 9, 11), [1stNotice]) -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Adding Business Days only
On Sun, 16 Mar 2008 15:06:02 -0700, TotallyConfused
wrote: Thank you John for responding. I am sorry if I have confused you and not explained completely. My first example is from a form where user enter date in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from 1stNotice date. This is an event in my form for 1stNotice field. Private Sub 1stNotice_Click() Me.IstNotice = Date Me.Noticedue = DateAdd("w",7,[1stNotice]) End Sub This code does not in the least resemble what you describe. What you describe is "add 3 days". Your code adds seven days. Do you want to add three days? or do you want to add seven days? My second example is for separate Report where I have similar issue. But it is not in an event it is in the control source for textboxes. The expression will be the same - just prefix it with an = sign for a control source. In this report, I have one text box that enter Now(). In my second text box I add 3 days to the Now(). Now() does not return today's date. it returns today's date AND TIME, accurate to a few microseconds. If you don't need the time portion, use Date() instead of Now(). However, I need workdays/business days only. So if it is Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday. The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) AGAIN: I explained how to do this. You evidently didn't understand it or didn't try it. Let's try again. Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21# DayDue = DateAdd("d", IIF(Weekday(Daysent) 3, 5, 3), DaySent) This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 - Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or less (Sunday, Monday, Tuesday) it will add 3 days. Hope this makes better sense. I hope that you will still be able to help. Thank you very much for your time and patience. -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Adding Business Days only
7 Seven days for 1st Notice in my form. 3 days for my report. Thank you.
"John W. Vinson" wrote: On Sun, 16 Mar 2008 15:06:02 -0700, TotallyConfused wrote: Thank you John for responding. I am sorry if I have confused you and not explained completely. My first example is from a form where user enter date in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from 1stNotice date. This is an event in my form for 1stNotice field. Private Sub 1stNotice_Click() Me.IstNotice = Date Me.Noticedue = DateAdd("w",7,[1stNotice]) End Sub This code does not in the least resemble what you describe. What you describe is "add 3 days". Your code adds seven days. Do you want to add three days? or do you want to add seven days? My second example is for separate Report where I have similar issue. But it is not in an event it is in the control source for textboxes. The expression will be the same - just prefix it with an = sign for a control source. In this report, I have one text box that enter Now(). In my second text box I add 3 days to the Now(). Now() does not return today's date. it returns today's date AND TIME, accurate to a few microseconds. If you don't need the time portion, use Date() instead of Now(). However, I need workdays/business days only. So if it is Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday. The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) AGAIN: I explained how to do this. You evidently didn't understand it or didn't try it. Let's try again. Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21# DayDue = DateAdd("d", IIF(Weekday(Daysent) 3, 5, 3), DaySent) This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 - Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or less (Sunday, Monday, Tuesday) it will add 3 days. Hope this makes better sense. I hope that you will still be able to help. Thank you very much for your time and patience. -- John W. Vinson [MVP] |
#18
|
|||
|
|||
Adding Business Days only
Mr. Vinson, I finally got a chance to go over everything calmly. Apply it
and test it. Thank you so much for your expertise, patience and for this forum. It worked!!! Just what I needed to complete this db. Instead of giving up on Access it only makes me more determine to keep learning and applying all that this forum has to teach and offer. Thank you all very much for your responses. I most definitely will use, test and apply your examples when needed. "John W. Vinson" wrote: On Sun, 16 Mar 2008 15:06:02 -0700, TotallyConfused wrote: Thank you John for responding. I am sorry if I have confused you and not explained completely. My first example is from a form where user enter date in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from 1stNotice date. This is an event in my form for 1stNotice field. Private Sub 1stNotice_Click() Me.IstNotice = Date Me.Noticedue = DateAdd("w",7,[1stNotice]) End Sub This code does not in the least resemble what you describe. What you describe is "add 3 days". Your code adds seven days. Do you want to add three days? or do you want to add seven days? My second example is for separate Report where I have similar issue. But it is not in an event it is in the control source for textboxes. The expression will be the same - just prefix it with an = sign for a control source. In this report, I have one text box that enter Now(). In my second text box I add 3 days to the Now(). Now() does not return today's date. it returns today's date AND TIME, accurate to a few microseconds. If you don't need the time portion, use Date() instead of Now(). However, I need workdays/business days only. So if it is Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday. The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) AGAIN: I explained how to do this. You evidently didn't understand it or didn't try it. Let's try again. Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21# DayDue = DateAdd("d", IIF(Weekday(Daysent) 3, 5, 3), DaySent) This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 - Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or less (Sunday, Monday, Tuesday) it will add 3 days. Hope this makes better sense. I hope that you will still be able to help. Thank you very much for your time and patience. -- John W. Vinson [MVP] |
#19
|
|||
|
|||
Adding Business Days only
Yes, tblHolidays is a table of holidays, I typically have 2 fields:
HolidayDate which is a Date/Time field, and is the one used in the code, and HolidayName which is a text field. I put in any date, other than a weekend which is handled automatically in the code, that is a holiday, or in the case I originally wrote this for, a bank holiday. You would use the code by setting a textbox to: =GetBusinessDay(#3/25/08#, 3) or from today's date: =GetBusinessDay(Date(),3) To subtract 3 days, use: =GetBusinessDay(Date(),-3) Simple as that. It will add or subtract the number of business days from the date you specify. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "TotallyConfused" wrote in message ... Thank you for your response. It seems your function looks at a Holiday table. What would this table consist of? Just Dates? I am sorry for the questions but I new to code and am trying to understand and apply. Sorry if I sound anal but I am trying to incorporate something simple to apply to what I already have. I don't have much time in fixing this. I have two instances where I need to add business days. The first is a form. The second is a report. How do I incorporate your function. I know to save it to Module, how do I call it from either of my samples below. Thank you in advance for your help. The following is in my subform field 1stNotice, Event Click: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) "Arvin Meyer [MVP]" wrote: The following has been working for me for about 10 years: http://www.datastrat.com/Code/GetBusinessDay.txt -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "TotallyConfused" wrote in message ... Can someone please help how to revise the following by adding only 7 "Business Days". The following is in my form field date on Click Event: Private Sub 1stNotice_Click() Me.1stNotice= Date Me.Noticedue = DateAdd("w", 7, [1stNotice]) End Sub The following is in my report field date controls: Daysent =Now Daydue=DateAdd("w",3,Now()) Thank you in advance for any help you can provide. |
|
Thread Tools | |
Display Modes | |
|
|