• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Ignore weekends when looping

Hi,

I have a form that posts events to a db. If an event runs from 26/1/20??  to 30/1/20?? four seperate events are recorded in the db (1 ea for 26,27,28 &29).

I need to add a procedure to ignore weekends.
ie. If an event starts on 21/9/2012 and runs for 3 days I want records created for the 21st, 24th & 25th thus ignoring the weekend.

I have seen plenty of examples for identifying workdays but nothing that I've been able to adapt to the current script.

This is the script that posts my consecutive records

Private Sub Command18_Click()
Dim rst As DAO.Recordset
Dim dtLeave As Date

Set rst = CurrentDb.OpenRecordset("tblLeaveEvent")
dtLeave = Me.LeaveStart

Do Until dtLeave = Me.LeaveFinish
    rst.AddNew
    rst!UserID = Me.UserID
    rst!LeaveType = Me.LeaveType
    rst!LeaveStart = dtLeave
    rst!LeaveFinish = dtLeave + 1
    rst.Update
    dtLeave = dtLeave + 1
Loop
rst.Close
Set rst = Nothing
   
End Sub


I expect that I will need to modify the above to accomodate the week days only requirement but I have no idea how.

Thanks for your assistance.
dj
0
dj1710
Asked:
dj1710
  • 7
  • 6
  • 2
  • +1
1 Solution
 
sameer_goyalCommented:
You can base your decision to consider or exclude a date on the WeekDay() function

or to be more clear WeekDayName() function.

Both these functions take a date type parameter and return either the logical number that represent the day of the week  like 1 for Sunday, 2 for Monday and so on

The WeekDayName function will return the name of the day like Friday, Monday etc,

So you can set the dtLeave variable to dtLeave + 2 if the DayOfWeek is Sunday or 7 and so on.

Let me know if this works for you.
0
 
dj1710Author Commented:
Hi sameer,

I'm an absolute novice and don't understand what you've written.
I don't have a weekDayName() Function are you suggesting that I add one? If yes how does it work with my current script? What stops it interfering with the loop?
0
 
sameer_goyalCommented:
Hey,

WeekDay() and WeekDayName() are built in functions in VBScript.

Atleast that is what appears you are using from the sample code above.

YOu can simply use them without bothering to create them. Let me know if you have further doubts.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
No matter what you do, this line determines the loop:

  Do Until dtLeave = Me.LeaveFinish

So what are you trying to do?

Anyway, you can use this function to skip weekends:

Public Function DateSkipWeekend( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date

' Purpose: Calculate first working day equal to or following/preceding datDate.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 1999-07-03, Gustav Brock, Cactus Data ApS, Copenhagen
  
  Const cintWorkdaysOfWeek As Integer = 5

  Dim bytSunday   As Byte
  Dim bytWeekday  As Byte
  
  bytSunday = Weekday(vbSunday, vbMonday)
  bytWeekday = Weekday(datDate, vbMonday)
  
  If bytWeekday > cintWorkdaysOfWeek Then
    ' Weekend.
    If booReverse = False Then
      ' Get following workday.
      datDate = DateAdd("d", 1 + bytSunday - bytWeekday, datDate)
    Else
      ' Get preceding workday.
      datDate = DateAdd("d", cintWorkdaysOfWeek - bytWeekday, datDate)
    End If
  End If

  DateSkipWeekend = datDate

End Function

Open in new window

/gustav
0
 
dj1710Author Commented:
Hi gustav,

That didn't work at all!

It created a weeks worth of single day events for every day of leave. ie 4 days leave was posted as 28 single leave events (4x7day weeks).

Obviously there is more to it than just randomly adding the function that you wrote.
Does it have a specific location?
0
 
Gustav BrockCIOCommented:
I don't know. It is not clear which result you want as there is a fixed count of days between Me.LeaveStart and Me.LeaveFinish

/gustav
0
 
IrogSintaCommented:
Try changing your code this way:
Do Until dtLeave = Me.LeaveFinish
    If Weekday(dtLeave, vbMonday)<6 Then
        rst.AddNew
        rst!UserID = Me.UserID
        rst!LeaveType = Me.LeaveType
        rst!LeaveStart = dtLeave
        rst!LeaveFinish = dtLeave + 1
        rst.Update dtLeave = dtLeave + 1
    End If
Loop

Open in new window

0
 
dj1710Author Commented:
Hi IrogSinta,

I was hoping that you'd come across this question.

I have tried modifying the script as suggested but I am getting a run-time error 3001 'invalid argument'.
Allenbrowne suggest that it could be a reference error but no references are marked as missing.

The line that gets highlighted when I run the code is the final line before the End If  'rst.Update dtLeave = dtLeave + 1'

Do you know what could be causing this and how to rectify it.

Full Code
Private Sub Command18_Click()
Dim rst As DAO.Recordset
Dim dtLeave As Date

Set rst = CurrentDb.OpenRecordset("tblLeaveEvent")
dtLeave = Me.LeaveStart

Do Until dtLeave = Me.LeaveFinish
    If Weekday(dtLeave, vbMonday) < 6 Then
        rst.AddNew
        rst!UserID = Me.UserID
        rst!LeaveType = Me.LeaveType
        rst!LeaveStart = dtLeave
        rst!LeaveFinish = dtLeave + 1
        rst.Update dtLeave = dtLeave + 1
    End If
Loop
rst.Close
Set rst = Nothing
   
End Sub
0
 
IrogSintaCommented:
Sorry about that, I posted from my phone and it messed up the carriage returns.
Here's the correct version:
Do Until dtLeave = Me.LeaveFinish
    If Weekday(dtLeave, vbMonday)<6 Then
        rst.AddNew
        rst!UserID = Me.UserID
        rst!LeaveType = Me.LeaveType
        rst!LeaveStart = dtLeave
        rst!LeaveFinish = dtLeave + 1
        rst.Update
    End If
    dtLeave = dtLeave + 1
Loop

Open in new window

0
 
dj1710Author Commented:
Thanks again Irogsinta.

You are a legend!
0
 
IrogSintaCommented:
Actually I'm only a Wizard, but you're welcome.
:-)
0
 
dj1710Author Commented:
Hi IrogSinta,

My accolades may have been premature.
I have just noticed a problem with the way my events are being recorded.

If I have a 3-days event it is being successfully recorded as three seperate 1-day events however, a fourth event record for the entire 3-days period is also created.
How do I prevent the multi-day record being created?

If you think that this is a new question I will post it as a new question.

regards,
dj
0
 
IrogSintaCommented:
I don't see how a multi-day record could be created.  I even ran a test and it seems to work fine.  Are you sure you did not have that record already in the table before you ran this routine?
0
 
dj1710Author Commented:
Hi IrogSinta,

I just ran it again for a period 7/9/12 to 12/9/12.

Four records were created the 1st is for the full period and then a single record for each workday within the period.

see attached file
0
 
IrogSintaCommented:
You forgot to attach the file.
0
 
IrogSintaCommented:
The problem is not with the code but in the form itself.  Refer to the answer in the followup question here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27869889.html#a38412272
0
 
dj1710Author Commented:
yep.

it's all working perfectly. Apparently you can teach an old dog new tricks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now