Solo712
Registered User.
- Local time
- Today, 14:20
- Joined
- Oct 19, 2012
- Messages
- 838
Boy, that's embarrassing. I was positive that I changed that line of code when you posted it originally because you even said to. Obviously I did not change it like I thought I had.
I changed it to the name of the transitdays control and it still works; however, it is still not bringing up the correct value unfortunately. It appears as though it ONLY takes into account the transit days and nothing else (weekends, etc).
For example, If I enter a duedate of 3/30 (which is a Monday) your code returns 3/29 (which is Sunday) it should return 3/27 (Friday).
Edit: After playing around with some other customers of varying transit days, it seems to only have an issue with customers whose transitday value is 1. Every other transitday value seems to work correctly
Ok, I see what the problem is. The formula fails to check the special status of Monday as the 'remaining day', ie. if Monday happens to be the day after shipping.
Code:
Public Function LatestShipDate(DueDate As Date, TransitDays As Integer) As Date
Dim i As Integer, z As Integer, tdate As Date
tdate = DueDate
i = TransitDays
Do
z = DCount("*", "tblHolidays", "HolidayDate = #" & Format(tdate, "mm/dd/yyyy") & "#")
If z = 1 Then
tdate = tdate - 1
ElseIf Weekday(tdate, vbSaturday) < 3 Then
tdate = tdate - Weekday(tdate, vbSaturday)
Else
i = i - 1
tdate = tdate - 1
[COLOR=red]If i = 0 And Weekday(tdate, vbSaturday) = 2 Then[/COLOR]
[COLOR=red] tdate = tdate - 2[/COLOR]
[COLOR=red] End If[/COLOR]
End If
Loop Until i = 0
LatestShipDate = tdate
End Function
Ok, so the highlighted added code in the routine handles the situation (incidentally, you would get the same wrong reading if the due date was 31/3 and transit days were 2, etc.). If the day after ship date falls on Monday, the ship date is Friday, not Sunday. This is what the red text says. Hope, I got it right, now

Best,
Jiri