Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Ignore weekends when looping

Posted on 2012-09-17
17
Medium Priority
?
322 Views
Last Modified: 2012-09-18
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
Comment
Question by:dj1710
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 5

Expert Comment

by:sameer_goyal
ID: 38408263
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
 

Author Comment

by:dj1710
ID: 38408286
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
 
LVL 5

Expert Comment

by:sameer_goyal
ID: 38408292
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38408368
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
 

Author Comment

by:dj1710
ID: 38408394
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38408447
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38409191
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
 

Author Comment

by:dj1710
ID: 38411869
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38411877
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
 

Author Comment

by:dj1710
ID: 38411998
Thanks again Irogsinta.

You are a legend!
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38412016
Actually I'm only a Wizard, but you're welcome.
:-)
0
 

Author Comment

by:dj1710
ID: 38412159
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38412197
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
 

Author Comment

by:dj1710
ID: 38412219
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38412222
You forgot to attach the file.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38412276
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
 

Author Comment

by:dj1710
ID: 38412283
yep.

it's all working perfectly. Apparently you can teach an old dog new tricks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question