EXCLUDE WEEKEND DATES*** SHIP DATE - TOTAL DAYS = START DATE

[Guest]

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.

 

[Guest]

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

 

[Guest]

I am sorry but I don't understand on how to do that?

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

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.

 

[Guest]

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

I am sorry but I don't understand on how to do that?

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

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.

 

[Guest]

Kristal,

I figured out the linking part. So here's the example in its entirety.

test.mdb has a table shipping with 3 flds ship_date (date), total_days (int)
and start_date (date)

Query1 lists these three fields plus a field called calc_start_date defined as
calc_start_date: subtract_date([ship_date],[total_days])

I changed the sub to a function with the code as shown below. When I run
the query, the calc'd field displays the appropriate start date.

Let me know if that works.

Jack

Public Function subtract_date(ship_date As Date, total_days As Integer)
Dim start_date As Date
Dim i As Integer
start_date = ship_date
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
subtract_date = start_date
End Function