Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Access VBA for 4 on 4 off leave calendar

Posted on 2010-11-22
Medium Priority
562 Views
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
``````
0
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
• 2

LVL 5

Expert Comment

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

lludden earned 1000 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
``````
0

LVL 51

Expert Comment

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
``````
0

LVL 51

Assisted Solution

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

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

/gustav
0

## Featured Post

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month9 days, 19 hours left to enroll