vb - add date skip weekends

  • Thread starter Thread starter jb33
  • Start date Start date
J

jb33

say, I have a function set up where a job is given a
start date and a number of days to complete. Then a
workdays table is populated with the appropriate number
of records by incrementing a date and adding a record.
Within this loop, I want to test for dayofweek(nextdate)
= Saturday. If true, add two days to get us to Monday,
then continue the loop - add the record.

But I don't see an obvious way of doing it. I see Access
has the day function but it seems to only apply to field
formats, and vb has some date format options but not one
that returns the day of week. Am I missing something or
does this get overly complicated.

Here's the code including the test roughly the way I'd
like it to work:


daysleft = Me![days_required]
nextdate = Me![start_date]
Do While daysleft > 0
If daysleft > 1 Then
daysrequired = 1
Else
daysrequired = daysleft
End If
With rs
.AddNew
!activity_id = Me![activity_id]
!Date = nextdate
!days_required = daysrequired
.Update
End With
if dayofweek(nextdate = "Friday" then
nextdate = DateAdd("d", 3, nextdate)
else
nextdate = DateAdd("d", 1, nextdate)
end of
daysleft = Me![days_required] - counter
Loop
 
Not sure why your table structure requires adding a series of "countdown"
records...... but, VBA has a built-in function to return the "day of the
week" for a date. Check out the DatePart function:

DatePart("w", Date(), vbSunday)

as an example. Seems as if it's just what you need.
 
thanks ken, datepart didn't come up in my search for date
in the help files. grrr! just the thing.

countdown is so work can be explicitly assigned to
particular days, then changed as the team schedule
requires.

jb
-----Original Message-----
Not sure why your table structure requires adding a series of "countdown"
records...... but, VBA has a built-in function to return the "day of the
week" for a date. Check out the DatePart function:

DatePart("w", Date(), vbSunday)

as an example. Seems as if it's just what you need.
--
Ken Snell
<MS ACCESS MVP>




say, I have a function set up where a job is given a
start date and a number of days to complete. Then a
workdays table is populated with the appropriate number
of records by incrementing a date and adding a record.
Within this loop, I want to test for dayofweek (nextdate)
= Saturday. If true, add two days to get us to Monday,
then continue the loop - add the record.

But I don't see an obvious way of doing it. I see Access
has the day function but it seems to only apply to field
formats, and vb has some date format options but not one
that returns the day of week. Am I missing something or
does this get overly complicated.

Here's the code including the test roughly the way I'd
like it to work:


daysleft = Me![days_required]
nextdate = Me![start_date]
Do While daysleft > 0
If daysleft > 1 Then
daysrequired = 1
Else
daysrequired = daysleft
End If
With rs
.AddNew
!activity_id = Me![activity_id]
!Date = nextdate
!days_required = daysrequired
.Update
End With
if dayofweek(nextdate = "Friday" then
nextdate = DateAdd("d", 3, nextdate)
else
nextdate = DateAdd("d", 1, nextdate)
end of
daysleft = Me![days_required] - counter
Loop
Click to expand...


.
Click to expand...
 
Back
Top