Kristal,
Ok. I wrote a procedure in a module of a test.mdb file, shown below. When
I run it, it skips over the Saturday and Sunday between 3/22 & 3/13 and comes
up with the start_date of 3/13. The part I don't know is how to hook this
logic up to the data tables to produce the desired result.
Jack
Public Sub subtract_date()
Dim ship_date As Date
Dim start_date As Date
Dim total_days As Integer
Dim i As Integer
ship_date = #3/22/2007#
start_date = ship_date
total_days = 7
For i = 1 To total_days
start_date = start_date - 1
If ((start_date Mod 7)) = 1 Then
start_date = start_date - 2
End If
If ((start_date Mod 7)) = 0 Then
start_date = start_date - 1
End If
Next i
MsgBox ("ship_date = " & ship_date & _
" , total_days = " & total_days & _
" , start_date = " & start_date)
End Sub
Kristaltips said:
I am sorry but I don't understand on how to do that?
Jack Jennings said:
Kristal,
I'm not an access programmer, but in other languages I would write a routine
which loops through the days from SHIP DATE counting back TOTAL DAYS number
of times.
If on a given day in the loop its remainder modulo 7 comes out as a Sat or
Sun then that day is skipped over.
Hope this helps.
Jack
Kristaltips said:
Please help. I would like the Start Date to be automatically entered.
i.e. SHIP DATE - TOTAL DAYS = START DATE
I can do this with the formula dateAdd("y",[SHIP DATE],[TOTAL DAYS]) and
this works but I need it to exclude weekends (sat / sun).
I have tried the dateAddW function but it gives me an odd date as it only
works for + not -
Please help.