Question Adding weekdays to a date (1 Viewer)

[cbrace09]

Table - tbl_Customers
CustomerID - AutoID
TurnTime - Number ( this is enterd by weekdays such as the number 14)
etc... fields

Table - tbl_Cases
CaseID - AutoID
ORDate - Short Date ( default value is Date() )
CustomerID - Number
etc... fields

Form - frm_Cases ( bound to tbl_cases )
Case ID - txtbox
Customer ID - Combo Box ( looks up CustomerID, CompanyName, and TurnTime in tbl_Customers )
ORDate - txtbox ( shows today's date by default )
* The Problem * Deadline - txtbox

I want the Deadline txtbox to add the ORDate Value to the TurnTime column in the CustomerID combo box.

IE -
ORDate = 9/11/2009
TurnTime Column = 14
DeadlineDate = Should display 9/30/2009 ( only counting 14 weekdays )

How can I do this?

THANKS!!!!!

 

[HiTechCoach]

Here is code that should do what you want.

fNetWorkdays and fAddWorkDays

 

[cbrace09]

Thanks Coach but I'm afraid all that code is way over my head and looks like it would me hours to adjust to work with my fields.

Also,

I'm not sure if I explained correctly but....
That code seems to require an end date, which I don't have....that's actually what I'm trying to calculate.

IE - if a customer's TurnTime is "14" then the Deadline would be 14 business days after the ORDate.
Note - I'm not concerned with Holidays

Does that change anything? Is there a easier way to do it than that code you sent me to?

Thanks again Coach, you're always a huge help!

 

[gemma-the-husky]

heres a not very elegant function

function deadlinedate(indate as date, daystoadd as long) as date
dim x as long
dim newdate as date

newdate=indate

for x=1 to daystoadd
retry:
  newdate=newdate+1
  if weekday(newdate)=vbsaturday or weekday(newdate)=vbsunday then goto retry
next

deadlinedate=newdate     
end function

this will add days one at a time, jumping over weekends, until it reaches the required working day count. it could be made more elegant but it should work ok

 

[HiTechCoach]

Thanks Coach but I'm afraid all that code is way over my head and looks like it would me hours to adjust to work with my fields.

Also,

I'm not sure if I explained correctly but....
That code seems to require an end date, which I don't have....that's actually what I'm trying to calculate.

IE - if a customer's TurnTime is "14" then the Deadline would be 14 business days after the ORDate.
Note - I'm not concerned with Holidays

Does that change anything? Is there a easier way to do it than that code you sent me to?

Thanks again Coach, you're always a huge help!

I should have been more clear about how to use the code.

You will want to paste all the code into a new code module. There is nothing that you need to modify ion the code. You simple call the function fAddWorkdays with your parameters.

Example:

? fAddWorkDays(#9/11/2009#,14)
10/1/2009

Not sure why in your example you have got 9/30/2009. If you are including the starting data in the range then op will need to simply subtract one day.

Like this:

? fAddWorkDays(#9/11/2009#,14) -1
9/30/2009

Hopw this helps ...

 

[cbrace09]

I know this will sound dumb to you but how do I call the function? How do I get this code to run immediatley after the ORDate field is populated and display the result of the module in a text box?

 

[HiTechCoach]

You could set the control property of a text box to be something:

=fAddWorkDays([txtORDate ], [txtTurnTime])

Where there is a:

text box named: txtORDate
text box named: txtTurnTime

 

[cbrace09]

Thanks for taking the time to reply Coach, but I'm still having problems....

I did finally get another method to work but it required several text boxes.

Bassically....

I had the CustomerID combo lookup the TurnTime field (it's hidden in the cbo box).

On the After update event I have the following code:

Private Sub cbo_Customer_Selector_AfterUpdate()

' Get OR DATE
If Me.txt_Day_Of_Week.Value = "Friday" Then
Me.txt_OR_Date.Value = Date + 3
Else:
If Me.txt_Day_Of_Week.Value = "Saturday" Then
Me.txt_OR_Date.Value = Date + 2
Else:
If Me.txt_Day_Of_Week.Value = "Sunday" Then
Me.txt_OR_Date.Value = Date + 1
Else:
If Time() > "2:00 PM" Then
Me.txt_OR_Date = Date + 1
Else:
Me.txt_OR_Date = Date
End If
End If
End If
End If
' End Get OR DATE

'Get OR NUMBER
Me.txt_OR_Number.Value = Me.txt_OR_Date & " - " & Me.cbo_Customer_Selector.Column(4) ' Each customer is assigned an ORGroup when entered into the db
' End Get OR NUMBER

' Get Deadline Date1 and Deadline Day Of Week
Me.txt_Deadline_Date1.Value = Me.txt_OR_Date + Me.cbo_Customer_Selector.Column(3)
Me.txt_Deadline_Day_Of_Week.Value = WeekdayName(Weekday(txt_Deadline_Date1()))

' End Get Deadline Date and Deadline Day Of Week
' Get Deadline Date 2
If Me.txt_Deadline_Day_Of_Week.Value = "Saturday" Then
Me.txt_Deadline_Date2.Value = Me.txt_Deadline_Date1 + 2
Else:
If Me.txt_Deadline_Day_Of_Week.Value = "Sunday" Then
Me.txt_Deadline_Date2.Value = Me.txt_Deadline_Date1 + 3
Else:
Me.txt_Deadline_Date2.Value = Me.txt_Deadline_Date1
End If
End If

Me.ORDate.Value = Me.txt_OR_Date
Me.txtTurnTime.Value = Me.CustomerID.Column(3)
Me.txtORDate.Value = Me.txt_OR_Date
Me.ORNumber.Value = Me.txt_OR_Number
Me.DeadlineDate.Value = Me.txt_Final_Deadline_Date
Me.fAddWorkDays.Value = fAddWorkDays([txtORDate ], [txtTurnTime])

there is more code but it doesn't pertain to this issue at all.

Anyway, so I added the two txt boxes as you suggested (since there werent named exactly as I already had them, just for troubleshooting)
and I added another textbox named fAddWorkDays

You can see at the end of the code where I have it poplulated. You are prolly wondering why I am populating some other those other fields...the reason is this is a data enty form where two people will be entering about 2000 records per month. I designed the code above so that the visible boxes could be unbound which allows them to keep the same value when the user selects to enter a new record. I do this because most of the records will have the same customer information.

Anyway...thanks again Coach!!!