Solved

Access VBA for 4 on 4 off leave calendar

Posted on 2010-11-22
4
553 Views
Last Modified: 2012-05-10
I am a firefighter and work a 4 days on 4 days of roster. I have found a great Access database for planning and managing leave. Only problem is it compiles the leave data for staff based on monday to friday - "If Weekday(MyDate, vbMonday) - 1 < 5" in below code. What code could I substitute here to select days on a 4 on 4 off basis?


For d = 1 To 31
If d > LastDayOfMonth Then
Temp = "-"
Else
MyDate = DateSerial(2000 + MyYear, m, d)
    If Weekday(MyDate, vbMonday) - 1 < 5 Then
    LData.MoveFirst
    Temp = "."
    Do While Not LData.EOF
        If LUsers!uinits = LData!LInits Then
            If MyDate >= LData!LDateFrom And MyDate <= LData!LDateTo Then
            Temp = LData!LType
            Exit Do
            End If
        End If
   LData.MoveNext
    Loop
    Else
    Temp = "."
    End If
End If

Open in new window

0
Comment
Question by:Joey_1978
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Expert Comment

by:Tompa99
ID: 34187725
Hi,

 If Weekday(MyDate, vbMonday) - 1 < 5 Then

 If Weekday(MyDate, vbMonday) - 1 < 4 Then

Then it will be from Monday to Thursday I think.

Best Regards Tompa
0
 
LVL 18

Accepted Solution

by:
lludden earned 250 total points
ID: 34187959
You need to define a start day, so say your first work day in a week was 2010-10-15.

So what you need to do is figure the number of days since then, and divide by 4.  If the number is even, it is a day you work, if odd, it is a day you have off.

Use this function
If IsWorkingDay(MyDate) Then

in place of
If Weekday(MyDate, vbMonday) - 1 < 5 Then

Private Const StartingDate as Date = #10-15-2010#

Public Function IsWorkingDay(DateToCheck As Date) As Boolean
     If (DateDiff("d",StartingDate,DateToCheck) \ 4) Mod 2 = 0 Then
          IsWorkingDay = True
     Else
          IsWorkingDay = False
     End If
End Function

Open in new window

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 34188095
You need something like that for the Swedish 3-3-System and its numbering method for the 3-days-work and 3-days-off periods crossing the normal 7-day week boundaries as well as calendar years.

Thus, first of all, a fixed date that marks the beginning of the sequences.
Then, modification of the code below from a 3-3 sequence to a 4-4 sequence. Should be easy, but if you need assistance, please tell.

/gustav
Option Compare Database
Option Explicit

' 3-3-System methods.
' 2000-01-22. Cactus Data ApS, CPH.
  
  ' First date of the 3-3-system is by definition Monday 1998-04-06.
  Private Const pdatThreeThreeCyclusPrimo     As Date = #4/6/1998#
  ' Length of the 3-3-system cyclus by definition.
  Private Const plngThreeThreeCyclusLength    As Long = 6
  ' Value of the 3-3-system cyclus' first day, numeric: 1.
  Private Const plngThreeThreeCyclusFirstNum  As Long = 1
  ' Value of the 3-3-system cyclus' first day, alpha: "A".
  Private Const plngThreeThreeCyclusFirstChr  As Long = &H41
'

Public Function ThreeThreeCyclusDay( _
  ByVal datDate As Date, _
  Optional booChar As Boolean) As Long

  Dim lngThreeThreeCyclusDay          As Long
  Dim lngThreeThreeCyclusDayFirstVal  As Long
  
  If booChar = True Then
    lngThreeThreeCyclusDayFirstVal = plngThreeThreeCyclusFirstChr
  Else
    lngThreeThreeCyclusDayFirstVal = plngThreeThreeCyclusFirstNum
  End If
  lngThreeThreeCyclusDay = DateDiff("d", pdatThreeThreeCyclusPrimo, datDate) Mod plngThreeThreeCyclusLength
  
  ThreeThreeCyclusDay = lngThreeThreeCyclusDayFirstVal + lngThreeThreeCyclusDay
  
End Function

Public Function ThreeThreeCyclusDayChr( _
  ByVal datDate As Date) As String
  
  Dim strThreeThreeCyclusDayChr As String * 1
  
  strThreeThreeCyclusDayChr = Chr(ThreeThreeCyclusDay(datDate, True))
  
  ThreeThreeCyclusDayChr = strThreeThreeCyclusDayChr

End Function

Open in new window

0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 34188918
The 3-3-system® or ThreeThree® is described here:

http://www.tretre.se/korteng.shtml

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

628 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