VBA - How to Generate a Days On Off Table

**zmbd** · Mar 19 '14, 09:44 PM

I haven't look at your script yet as I want to make sure we have the basics for the work schedule correct:

- 7 day work week, that is Sunday thru to the following Saturday.

- You work 10 days in a row (so week 1 would start Sunday and you would work thru week 2 until Tuesday) and then have the next four days off (Wednesday thru Saturday) and then Week 3 start your next 10 days on duty starting Sunday.

- Your Co-Worker has 7 days on duty and 7 days off (so works all of week 1 has week 2 off and then works week 3 etc...); however, this is only for six months straight... then what, he's off duty for the remaining six months?

**pholness** · Mar 20 '14, 01:55 PM

Thanks for your reply! Intelligent and thorough!!

Please don't look at my script lol - I am quite embarrassed - I tried agian this morning and am very happy someone has made themselves available:

Now to your point(s):

1. Yes.

2. Yes. Let's use YEAR 2014 in this example:
I work 10 days in a row and four days off.
Week 1 (Dec 29, 13 thru Jan 4, 14): I would start Tuesday and end week 2 Thursday) and then have the next four days off (Friday thru Monday) and then Week 3 start my next 10 days on duty starting Tuesday.
**The Start and End of the WORK Schedule is as per contract and may not reflet the start and end of the work week as per Calander yr.

3. The day+1 (and onward) of end of the contract would not, ofcourse, need to be populated in the tbl as days off or on.

Did this help?

**zmbd** · Mar 20 '14, 02:41 PM

Rather than re-invent the wheel, I suggest that you look at the following example by Allen Browne, he's a well respected MVP and used to be quite the active memeber here:Recurring events - Allen Browne

See if this works for you, if not, if you can describe what isn't working we can most likely tweek Mr. Browne's example to fit.

**pholness** · Mar 20 '14, 07:15 PM

Well this is a big wheel! Thank you!! And Shout Outs to Mr Browne!!!

I like (especially the EDIT/CANNED ability), and with the following Minor/Major changes (which I can do :) with just a couple of exceptions) can you answer the questions below - don't forget the exceptions!!:

Minor
1. Rename EVENT START to SHIFT START
2. Rename RECUR COUNT to SHIFT COUNT (disabled)
3. Rename Ending to SHIFT END (enabled)
4. The DESCRIPTION field could be a dropdown with the varying 'Shift' model
5. The Comment field could be auto populate a dropdown with "Days On", and "Days Off" w/ Days On as the default

Major
1. The user should select Ending (Shift Start)
2. SHIFT COUNT should be generated not SHIFT END
3. Add EMPLOYEE NAME (and or other details) to form, linking each EVENT ID to an EMPLOYEE (should be minor)
4. The Dates generated per occurance should also calculate the off days. For example:
Currently, if there are 10 (10/4) shifts between March 6-14 and Dec 31-14, occurance dates for 1 thr 30 should be he begining of each Shift as noted in previous post above

Exceptions
1. Reverse SHIFT COUNT and SHIFT End calculation
2. Inculde days off in caluclation - we don't nessarily need to have it in each occurance, but for visual sake I would like to include days off as an occurance.

Questions
1. Can we Show Shift count as DAYS ON and then another 'shift count' as DAYS OFF - this way we can show 30+ occurances each for days on and off - for visual sake?
2. What is tblCount for - it seems to place a maximum on the amount of occurances - is this best practice or can it be removed?
3. Can you write the exceptions above?

**zmbd** · Mar 20 '14, 08:02 PM

Questions
1. Can we Show Shift count as DAYS ON and then another 'shift count' as DAYS OFF - this way we can show 30+ occurances each for days on and off - for visual sake?
2. What is tblCount for - it seems to place a maximum on the amount of occurances - is this best practice or can it be removed?
3. Can you write the exceptions above?

I've not analyzed AB database nor do I really have the time to do so at the moment.

We do like to keep the threads to one question however, as these are related to the linked information I'll do my best
1) AB provided a link at the bottom to MVP Duane Hookom MVP Duane Hookom where several examples are provided. See if one or more of these can provide you with the information needed, keeping in mind that some of these are quite dated.

2) Table count is an "expansion" table. What it does is allows for the Cartesian Product where every combination of records between the tables is created. This is explaned in the "Calculatin g Occurances" section of the link.

3) If it's something easy to do; however, currently I am not able to spend alot of time on the project :( . Please understand that I am a Chemist by trade and do this during the downtime between testing, maintance, calibrations, etc... therefore, I work on things in 5 and 10 minute groupings. To compound things, we're currently short staffed so alot of those down moments I'm now busy doing other things.

**pholness** · Mar 21 '14, 08:27 PM

I saw this prior to coming to bytes.com, but kind of glossed over it because its a big Wheel. Now I am studying it and making my changes as I go, it offers more than what I first thought we needed but still posses the issue of "Inculde days off in caluclation..." .

Currently, it reoccurs every '10' days on, but does not account for the '4' days off.

I'm looking into the qryEventDates to see if I can adjust the calcs:

Code:

EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))

**pholness** · Apr 2 '14, 04:16 AM

I got it to work! I did not have to adjust much of anything from AB's form except Exceptions point #1 and from each occurrence date or each recurring START DATE I was able to generate each individual days on and the days off script in vba and populate my tables + thanks again for shedding the light!

Here is the code:

Code:

MeEventDescrip1 = 11 'Me.EventDescrip '(b4 "/")
MeEventDescrip2 = 3 'Me.EventDescrip '(aftr "/")

For iShiftOn = 0 To DCount("EventID", "qryEventDates", "[EventID] = 126") 'Me.EventID
    StartDate = DLookup("[EventDate]", "qryEventDates", "[InstanceID] = " & iShiftOn & "")
        For DatesOn = 0 To MeEventDescrip1
            DateOn = StartDate + DatesOn
            DoCmd.SetWarnings False
            If DatesOn = MeEventDescrip1 Then DateOn = DateOn + MeEventDescrip2: GoTo NextME:
            On Error GoTo ExitME:
            DoCmd.RunSQL "INSERT INTO tbl_EmployeeShiftScheduleON (EmployeeID,ShiftSchedule,DateOn) VALUES (8,'6/2',#" & DateOn & "#)"
        Next DatesOn
NextME:
Next iShiftOn
ExitME:
DoCmd.SetWarnings True

**zmbd** · Apr 2 '14, 01:02 PM

happy to lend a hand...

... and THANK YOU for posting back your tweak, you've more than likely re-paid our help a hundred times over by helping someone else in a similar situation to yours with that information!

(^_^)